Resources
Dec 18, 2024 |
24 min
|
2.7k views
|

Connecting Shiny Apps to Databases with Posit Team

Many of us learned to work with data on our laptop, but at work data is increasingly stored in databases. Join us on Wednesday, Dec 18th at 11am ET with Sara Altman, who will lead us through building a Shiny app that reads from and writes to a database - using DuckDB 🦆

Sara will cover how to:
1. Set up a connection to DuckDB from a Shiny app
2. Query the database using R or Python
3. Use editable tables to enable users to write back to the database
4. Securely deploy your app to Posit Connect

Q&A Recording: https://youtube.com/live/C8j3d46AacM?feature=share

Resources mentioned in this Q&A:
✨ Connect Authentication documentation: https://docs.posit.co/connect/user/oauth-integrations/
✨ Git backed deployment in Posit Connect: https://posit.co/blog/git-backed-deployment-in-posit-connect/
✨ shinylive: https://posit-dev.github.io/r-shinylive/
✨ Mastering Shiny Best Practices: https://mastering-shiny.org/best-practices.html#best-practices

Blogs:
✨ https://blog.stephenturner.us/p/duckdb-vs-dplyr-vs-base-r
✨ https://outsiderdata.netlify.app/posts/2024-04-10-the-truth-about-tidy-wrappers/benchmark_wrappers.html
✨ Using Posit tools with data in DuckDB, Databricks, and Snowflake: https://posit.co/blog/databases-with-posit/
✨ Creating a Shiny app that interacts with a database: https://posit.co/blog/shiny-with-databases/
✨ Appsilon has a lot of resources on the topic: https://www.appsilon.com/post/r-shiny-duckd

Please note, while the monthly Posit Team Workflow Series is usually the last Wednesday of the month - this will be a week earlier. Cheers to a festive holiday season and a fantastic year ahead for you and yours! ✨

To add future Monthly Workflow Demo events to your calendar → https://www.addevent.com/event/Eg16505674

You can view all the previous workflow demos here →
https://www.youtube.com/playlist?list=PL9HYL-VRX0oRsUB5AgNMQuKuHPpNDLBVt

image: thumbnail.jpg

Transcript#

This transcript was generated automatically and may contain errors.

Hi everyone, my name is Sarah Altman and I'm on the developer relations team at Posit. Thanks for coming to this workflow demo. At the end of the session, there will be a live Q&A, at which point you can ask any questions.

Today I'm going to talk about connecting Shiny apps to databases with Posit Team. I'll show you an example app that can read from and write back to a DuckDB database, and I'll show you that app in both R and Python. I'm going to focus on how the app connects to and reads from the database, allows users to edit a table, and then writes those changes back to the database. I'll also talk about considerations for deploying your app, and show you how to deploy to Posit Connect. So let's get started.

Why connect Shiny to a database

So before we take a look at the app, let's first talk about why you would even want to connect your Shiny app to a database. One reason is that your organization might just store your data in a database, and you want to make a Shiny app that interacts with, displays, does something with that data. A second consideration is speed. Even if your data originates elsewhere, you can use DuckDB to speed up operations, which might be important if your app allows the user to do a lot of computations on the data. A third reason is if your app's functionality relies on a shared data source. As you'll see, the app we'll build today allows users to read and write to a central data source.

So let's talk about the general workflow. We're going to create an app that reads from a DuckDB database, allows users to edit that data by flagging suspicious values, and then writes those flagged values back to the database. As I said, we're going to use a DuckDB database, but the code we'll write can be adapted to a variety of database management systems. This related blog post linked here shows the same code we'll see today adapted for Snowflake and Databricks.

Overview of the app

So let's take a look at the app. This is the app that we'll be working on today. I've written the same app in both R and Python, and the GitHub repositories are linked below. This is an image of the Python app, but the R one looks pretty similar. The general idea of this app is to allow users to flag suspicious values in a dataset. That data is displayed in the plot on the left-hand side with a few outliers highlighted, and then those outliers are displayed in that table on the right-hand side. You can edit the flag column if you think a value might be an error or is in some way suspicious, and then write those flag values back to the database, at which point the idea is maybe someone would take a closer look at those rows of data or exclude suspicious values or something like that. So we're not really changing the underlying data, but we're just adding a flag.

Now let's take a look at the deployed version of the app so we can see it in action and interact with it.

So now we're looking at the deployed version of the app. So let's see how it works. On the left-hand side, we have this plot that's displaying the data. This is error quality data, so it's showing the date and the PPM, which is the ozone level in parts per million on that date. In blue are a couple of highlighted outlying values. So you can click on these values, and the corresponding row will highlight in this table. This is an editable table, which means that you can edit this value. And when we talk about the code in a minute, I'll show you how to implement that in your Shiny app.

I'm going to change this back. The point of the flag is if you identify a point that you think is not just an outlier but is suspicious in some way, you think it's an error, something went wrong, you can set the flag to 1 just to mark that there might be a problem with that row of data. And then you can click write to database to write that flag back to the database table.

And one other thing I want to highlight, as it says down here, this example app is reading from and writing to an in-memory DuckDB database, which means that when you refresh the page, the database is going to be regenerated from scratch. So you're not going to see any changes that you have written. This is solely for the purposes of the example. Obviously, this isn't really how you would want to implement this in real life, since you would want those updated flag values to persist. But for the purposes of the example, to make it so that it's not confusing if someone else who is using the app has changed the flags, but you don't know that they're using the app, and so you're wondering why suddenly all your flag values have been updated. This doesn't really change any of the code that we're going to talk about today, but this is just something to keep in mind.

Connecting to and reading from the database

Let's take a look at the code. Again, I'm going to focus on three aspects of the app. Connecting to and reading from the database, creating an editable table, and writing changes back to the database. I'm going to build the app in Posit Workbench, so let's open that up.

Okay, so now we're in Posit Workbench. I'm going to open up this VS Code session, and we'll take a look at the Python app.

App.py contains the code for the app, but I'm also going to make a new file just so we can copy and paste some of the code to make it less overwhelming. Like I said, I'm going to focus on just three elements. If you want to take a look at the other parts of the app, you can take a look at the code in the GitHub repository or ask questions about it in the Q&A. The three elements we're going to focus on, again, are connecting to and reading from the database, creating an editable table that allows the user to set those flags, and then writing back to the database.

So first, we want to connect and read from the database. So let's just run the libraries, and you'll notice that this interactive Jupyter window opens up on the right-hand side. We're going to use Ibis to connect to the database and read from it. Ibis is an open-source DataFrame library that works with a variety of backends, including DuckDB, Snowflake, and Databricks. So let's run this line. And now, Khan stores our connection.

We used connect to create that connection, and now let's grab the code that creates the table. So Khan just has the connection to the database. We also want to create a representation of one of the tables in the database, Ozone, so we can use that data in our app. So to do that, we'll use table, and this is going to create an Ibis table that we can then manipulate and use in our app. Okay, so let's run Ozone, just take a look at it. You'll notice that it's not showing you a preview of the data. It's just saying what columns there are and their types.

So one thing to know is that Ibis lazily evaluates commands, which means that the full query is never run on the database unless explicitly requested. If we want to preview Ozone in this interactive window, we can run ibis.options.interactive equals true. And this is going to turn on interactive mode, and now all our expressions are going to be executed immediately and then printed to the console, or when printed to the console. Okay, so now if we try to print Ozone to the console, it'll run the entire query to get the Ozone table, and we'll see a preview of it. So this is useful if you're trying to debug your app. You just want to know what the data looks like or something like that.

So one thing to know is that Ibis lazily evaluates commands, which means that the full query is never run on the database unless explicitly requested.

And then the rest of this code that I had moved, this is Ibis code that is renaming some columns and selecting some columns in preparation for the rest of the things we're going to do in the app. Let's just run that. Now you can see Ozone looks a little bit different.

One other thing I want to point out is this line here. So like I said, Ibis lazily evaluates commands, and it's not going to run the full query unless requested, or it has to. If you want to force the query to execute, you can use two pandas. So first I'm going to turn interactive mode off so you can see this. And what two pandas does is it evaluates the query fully and then returns the result as a pandas data frame. So if we just run Ozone, we'll see this similar output where it's just telling us the types. Now if we run Ozone to pandas, we'll get a pandas data frame back. So here I've done this in the app because we need the entire outliers table. Outliers is what's going to become that editable table, and we want the entire thing. So so far I've used Ibis to set up a connection to our DuckDB database and then created a table.

Creating an editable table

So now let's talk about creating that editable table component that displays in our Shiny app. So let's go back to app.py. This is a Shiny Express app, which means it doesn't have a separation between the UI elements and the server elements. So our editable table code is down here, and I'm going to copy it into the script. I'm not going to run it, but just so it's a little bit easier to see.

So all this creates an editable table. It's a data grid where I've set editable equal to true, which makes the cells editable. You can also do this for a data table component. So let's take a look at how that works. I'm just running the app. I have to disconnect because we're using the same connection.

Okay, so here's our app. And on the right-hand side, this is the editable table. And because we set editable to true, now we can change these values. And because we set editable to true, now we can change these values to one. Actually, all of the cells are editable. So you could change this to Pennsylvania. But I have some other code in the app that reverts it to the original because we don't want you actually changing the state or anything else. The point of the app is only to set the flag.

Writing changes back to the database

Let's go back to the code. So now we've talked about setting up the connection and then creating the editable table. And we want to focus on the third element, which was writing back to the database.

So let's take a look at app.py. All of this code at the end is responsible for writing back to the database when the user clicks that write to database function. I'm going to close this. We use the reactive event decorator so that this function is only triggered when the user clicks that button. This code is trying to figure out which values have been changed. So we know which values we need to update. I really just want to focus on these lines of code because these are the lines of code that are actually interacting with the database. So what this code is doing is for every row that we've changed, it's updating so that the table has the new flag that had been set by the user.

So you'll notice that there's a bit of SQL here. One advantage of IBIS is that you can write familiar Python instead of writing SQL to interact with the database. But I had to write a bit of SQL here because while some IBIS backends support updating existing tables using an IBIS method, the DuckDB backend doesn't. So I had to write a little bit of SQL and then execute it using the IBIS method raw SQL. So what this SQL is doing is updating flag to the new value where it was one of those ones that was changed. And then raw SQL is executing that query.

One advantage of IBIS is that you can write familiar Python instead of writing SQL to interact with the database.

Okay, so let's take a look at the app again. We've already set two flag values to one. Now when we click write to database, we get this little message that two values were successfully updated.

So in summary, we've talked about how to connect to the database with IBIS, create an IBIS table to represent our data.

Create an editable table component in our Shiny app so that the user can edit a column of data. And then how to write those changes back to our database.

Now I'm going to briefly talk about the R version of the app and then we'll talk about app deployment.

So now I'm going to open this RStudio session. So that we can take a look at the R app.

And I'm going to talk about the same three elements of the app, connecting and reading from the database, creating that editable table and then writing changes back to the database.

Connecting to the database in R

So our app primarily is an app.R. Let's first take a look at the connection code. So this is where we're connecting to the DuckDB database. Whereas in the Python app, we used IBIS. Here we're using the DBI package and dbconnect from the DBI package allows you to connect to various databases. We also need a driver. This is the DuckDB driver from the DuckDB package. And this code looks pretty similar to the Python one.

Again, we need to make a table to represent our ozone data. And now we're using table or TBL function from the dplyr package. And this is going to create a representation of our ozone data that exists in the Python package. So we're going to create a table. And now we're using table or TBL function from the dplyr package. And this is going to create a representation of our ozone data that, again, we can use in the app.

The editable table component in R

So now let's take a look at the editable table component.

Because this is a Shiny for R app, we have a separation of the UI components and the server function. So here, this is where we've defined the data table output component that is going to be our editable table. And then let's scroll down to the server function. And you see that inside render DT, there's this function create editable table. So this is just a helper function that I created. So let's take a look at that.

Here, we're creating a data table. And just the thing is, we're not going to create a table. So let's take a look at that. Here, we're creating a data table. And just the thing to note is that similar to the Python app, we can add an editable argument. And this time, we need to provide it a list of options. So we're saying that we want the cells to be editable and then to disable editing on columns 0 through 3. So that's all the columns except for the flag.

Writing changes back to the database

And now the third element is writing changes back to the database. So again, the similar concept. We need to figure out what has been changed and then update those values in the database. So here, we've used the DBI function, db execute, to write back to the database. DB execute takes a connection, so that's con, and a SQL string, and then executes that SQL. Since we want to update an existing table, we're using an update statement.

It looks pretty simple. It looks pretty similar to the Python one. The styling of the table is a little bit different. But again, we can edit the flag values and then write them back to the database.

The data table editable functionality is nice because it won't even allow you to edit the columns that are not the flag column.

Deploying the app to Posit Connect

So now let's deploy this app. One way is to click this blue Publish button, and we can publish it to Connect.

So let's name it Outliers App. Click Publish.

Okay, and here is our published app, ready to share with others.

Managing database access and credentials

One thing to keep in mind is that when deploying an app that interacts with the database, you'll probably have to think about managing access. There's a couple strategies to handling database permissions. So the first is that if you are deploying to Connect, you can use the Vars pane. To securely set environment variables after deploying your app. And Posit Connect will automatically provide the correct values to your app during runtime.

So say our database had a key that controlled access. We can add a var called dbKey. And then add the value of that key. Add the variable. Click Save. And then in our app, we would then, in the Connection code, reference that value. So we do.

That's the password. And then we would add a var called dbKey. And then we would add a var called dbKey. That's the password.

And now when we deploy the app, it'll get that value from Connect and be able to manage access that way.

So as you just saw, one way to deploy your app and manage access involves using environment variables in Posit Connect. This strategy relies on adding one set of credentials to Posit Connect, though. What if you want each user to authenticate with their own credentials? This could be particularly important if your database includes row-level or data-level permissions. One solution is to use OAuth integration, which is now available in Posit Connect for Databricks and Snowflake, allowing users to log in with their own accounts. With OAuth integration, you don't need to specify the database credentials used in your app. Instead, users can authenticate through the OAuth integration, and then Posit Connect will automatically handle the connection. For other database management systems, you can request user credentials through your app and then pass those credentials to the connection code.

One solution is to use OAuth integration, which is now available in Posit Connect for Databricks and Snowflake, allowing users to log in with their own accounts.

Summary

So now just to summarize what we talked about today, you saw how to create a shiny app that reads from and writes to a database. We used IBIS for the Python app to build a connection to DuckDB and read in a table. For the R version of the app, we used the DBI package for the same task. Then, to allow users of the app to make changes to the data, we created an editable table that lets you set a flag value. We then wrote the changes made by the user back to our original database table. And then we deployed the app to Posit Connect and discussed two possible ways to securely deploy your app.

I showed you one particular implementation of this style of app, an app that reads in data from a database, displays it, allows someone to edit that data, and then writes the data back to the database. But you can imagine this framework extending to other use cases, such as labeling images when training an image recognition system or something similar.

I've provided a few links to additional resources if you'd like to learn more. The first is the related blog post that includes sample code for creating shiny apps that interact with DuckDB, Snowflake, and Databricks. The second is the title of another workflow demo that covers how to use OAuth and Databricks to create a shiny app. The third is the integration in Posit Connect. And then I've also linked to the IBIS and DBI websites.

Thanks for attending this demo. I'm looking forward to your questions in the Q&A.