<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/access-your-data-in-google-bigquery-with-python-and-r/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://www.blendo.co/blog/access-data-google-bigquery-python-r/"> R – Blendo</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<div>
<div>
<div>
<p>Some time ago we discussed how you can access data that are stored in Amazon Redshift and PostgreSQL with Python and R. Let’s say you did find an <a rel="nofollow" href="https://www.blendo.co/" target="_blank">easy way</a> to store a pile of data in your <a rel="nofollow" href="https://www.blendo.co/integration/google-bigquery/" target="_blank">BigQuery data warehouse</a> and keep them in sync. Now you want to start messing with it using statistical techniques, maybe build a model of your customers’ behavior, or try to predict your churn rate.</p>
<p>To do that, you will need to <strong>extract your data from BigQuery</strong> and use a framework or language that is best suited for data analysis and the most popular so far are Python and R. In this small tutorial we will see how we can extract data that is stored in Google BigQuery to load it with Python or R, and then use the numerous analytic libraries and algorithms that exist for these two languages.</p>
<p>READ: <a rel="nofollow" href="https://www.blendo.co/blog/access-your-data-in-amazon-redshift-and-postgresql-with-python-and-r/" target="_blank">If you are looking to access your data in Amazon Redshift and PostgreSQL with Python and R</a></p>
<h2>Python</h2>
<h3>Connect to BigQuery with Python</h3>
<p>In order to pull data out of BigQuery, or any other database, we first need to connect to our instance. To do so, we need a cloud client library for the Google BigQuery API. Although the options are quite many, we are going to work with the Google Cloud Bigquery library which is Google-supported.</p>
<p>However, feel free to explore other third-party options like the BigQuery-Python library by tylertreat which is also widely used, well-documented and frequently maintained.</p>
<p>So, in order to be able to install the Google Cloud Library, we assume that you have already setup a Python Development Environment. If this is not the case you can refer to <a rel="nofollow" href="https://cloud.google.com/python/setup" target="_blank">Python Development Environment Setup Guide</a>.</p>
<p>The next step is to install the library and this can be easily achieved by executing the following command:</p>
<pre data-enlighter-language="shell">pip install --upgrade google-cloud-bigquery</pre>
<p>In order to be able to connect to the database, you need to download locally the .json file which contains the necessary credentials from within your service account. In case you didn’t already have a service account, you would have to create one and then download in our local machine the previously mentioned JSON.</p>
<p>Now that everything is set we can move on and write some Python code in order to initialize the connection.</p>
<pre data-enlighter-language="python">from google.cloud import bigquery

from google.oauth2 import service_account

credentials = service_account.Credentials.from_service_account_file(

    'path/to/file.json')

project_id = 'my-bq'

client = bigquery.Client(credentials= credentials,project=project_id)</pre>
<p>As you can see, the parameters you need to specify are the project_id and the location of the json key file. In Bigquery, a project is the top-level container and provides you default access control across all datasets.</p>
<h3>Executing Queries with Python</h3>
<p>With the BigQuery client, we can execute raw queries on a dataset using the query method which actually inserts a query job into the BigQuery queue.</p>
<p>These queries are executed asynchronously in the sense that there is no timeout specified and that the program will wait for the job to complete.</p>
<p>As long as the job is completed the query method returns a QueryJob instance according to documentation which, among others, contains the produced results.</p>
<p>For more details you can always refer to the official documentation <a rel="nofollow" href="https://googlecloudplatform.github.io/google-cloud-python/latest/bigquery/reference.html#google.cloud.bigquery.job.QueryJob" target="_blank">here</a></p>
<p>The Python code required follows:</p>
<pre data-enlighter-language="python">query_job = client.query(&quot;&quot;&quot;

  SELECT *

  FROM dataset.my_table

  LIMIT 1000&quot;&quot;&quot;)

results = query_job.result()  # Waits for job to complete.</pre>
<p>Note that the above query uses standard SQL syntax as the client library default to this. If you wish you can change the SQL dialect to legacy as follows:</p>
<pre data-enlighter-language="python">job_config.use_legacy_sql = True

query_job = client.query(&quot;&quot;&quot;

  SELECT *

  FROM dataset.my_table

  LIMIT 1000&quot;&quot;&quot;, job_config=job_config)

results = query_job.result()  # Waits for job to complete.</pre>
<p> </p>
<h2>R</h2>
<p>If Python is not your cup of tea and you prefer R instead, you are still covered. Getting your data from Google BigQuery is equally easy as in Python – or even easier.</p>
<h3>Connect to BigQuery with R</h3>
<p>Again we are going to use an open source library called <a rel="nofollow" href="https://github.com/r-dbi/bigrquery" target="_blank">BigrQuery</a>, which is created and maintained by Hadley Wickham, Chief Scientist at RStudio. In order to install we simply have to run the following command from within R console:</p>
<p>install.packages(“bigrquery”)</p>
<h3>Executing Queries with R</h3>
<p>According to the library documentation,  the first time we’ll need to authorize the application to access Google Cloud services. As documented on the Authentication section of the <strong>bigrquery GitHub page</strong>, we’ll follow the prompts within R to open the authorization URL and later copy the authorization code back to R. You’ll only need to authorize the library once, requests performed after the first authorized one will refresh access credentials.</p>
<p>So the overall procedure that we are going to follow can be summarized as:</p>
<ul>
<li>Import ‘BigrQuery’ library.</li>
<li>Specify the project ID from the Google Cloud Console like we did with Python.</li>
<li>Form your query string</li>
<li>Call query_exec with your project ID and query string.</li>
</ul>
<pre data-enlighter-language="matlab">#import library

library(bigrquery)

# Use your project ID here

project_id &lt;- &quot;your-project-id&quot; # put your project ID here

# Example query

sql_string &lt;- &quot;SELECT * FROM dataset.my_table LIMIT 1000&quot;

# Execute the query and store the result

query_results &lt;- query_exec(sql_string, project = project_id, useLegacySql = FALSE)</pre>
<p>Again, if you wish you can execute queries using legacy sql by changing the value of the useLegacySql to true in the query_exec function.</p>
<h2>Conclusion</h2>
<p>As you can see, getting your data from BigQuery for further analysis in Python and R is really easy. The true power of a database that stores your data in comparison with CSV files etc. is that you have SQL as an additional tool. Invest some time learning how to work with SQL and you will not regret it, having structured data in a database and using SQL to pre-process your data before you start building your statistical models will save you time and resources.</p>
<p>Although in this article we focused mainly on BigQuery, using any other database is equally easy. The main difference will be the selection of the appropriate library for Python and for R.</p>
<p>Would love to hear your thoughts in the comments below.</p>
<div></div>
</div>
</div></div>
<div>
<div>
<h2><a rel="nofollow" href="https://www.blendo.co/integration/google-bigquery/" target="_blank">Today’s not the day for “moderation”, no such thing as “I have some data”. Get all of your data in BigQuery, now!</a></h2>
</div></div>
<div>
<div>
<div>
<div>
<div>
<div>
<p>	</p>

<p></p>
<div></div>
</div>
</div></div>
</div>
</div></div>
<p>The post <a rel="nofollow" href="https://www.blendo.co/blog/access-data-google-bigquery-python-r/" target="_blank">Access your data in Google BigQuery with Python and R</a> appeared first on <a rel="nofollow" href="https://www.blendo.co/" target="_blank">Blendo</a>.</p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://www.blendo.co/blog/access-data-google-bigquery-python-r/"> R – Blendo</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/ZLoMTxE9wk4" height="1"></a></div>