Google Cloud Platform for Data Scientists: Using R with Google BigQuery, Part 2 (storing and retrieving data frames)

In a previous blog post, we talked about how to query public datasets for analysis and visualization in R. This is a great option when you have a dataset available on BigQuery (GCP’s fully-managed data warehouse service).

The natural next question you may have is, "How do I take data that I've processed in R and store it in BigQuery?" By doing that, you can query your data frame from BigQuery or stash analyzed data so your colleagues can import the data into their R projects or create data frames in other data science platforms, such as Python pandas.

In this blog post, you’ll learn (using bigrquery, an open source library for R created by Hadley Wickham) how to create an R data frame and stash it in BigQuery.

Then, you’ll see how to access that data frame later from the BigQuery web interface and RStudio.

Create a data frame to store in BigQuery

Let's inspect the frame:

Let's graph the data so that later we can visually compare the data frame we've created with the one returned from BigQuery.

The following image shows the data we've generated:

Store the data frame in BigQuery

Next, using the BigQuery web interface, create a new dataset named `test_dataset`.

You're now ready to write data to BigQuery. Noting your project name and cloud project ID, store your data frame into a new table named `data_stash`:

Congratulations, you've now saved your work to the Cloud! Let's verify it's there.

Observing the data using the BigQuery web interface

(Note: If your dataset isn't showing, inspect the Job History to see the status of your upload job. The time it takes to appear in BigQuery depends on the size of your upload job.)

Once the table appears in your datasets, you can see the schema that was calculated based on your data frame by clicking the table (labeled stash in this example) that your upload job created.

Now, let's preview the data to make sure that our data frames were published correctly by clicking on the preview tab. The data will preview as follows:

Retrieving the data from R

Inspecting the resulting frame shows the data's still there:

We can even verify the data by plotting just like we did before:

Wrapping up

For further exploration, you might want to try retrieving the data frame from pandas, using the JavaScript client library and D3.js to plot data, visualizing your data using Google Data Studio, or visualizing your data using Cloud Datalab.