Google BigQuery Execute SQL file from Python Script

Question

I have a python file that uses datalab.bigquery to execute a SQL query that is written out. However, I have this SQL query saved down in the same GitHub repository so am wondering if there is a way to run the github SQL file without having to copy and paste the query.

Currently it looks like this:

import datalab
import datalab.bigquery as bq
import pandas as pd 

df = bq.Query('''
                SELECT
                  CASE
                    WHEN advance_date IS NULL
                        AND release_date IS NULL
                        AND resale_close_date IS NULL
                            THEN TRUE
 ...
 '''_.to_dataframe()

This works fine, but when updates are made to the SQL script they are not reflected in this python script, which creates an issue. I would like it to call the SQL query that is saved in the Github repository. Is there a way to do this? Like df = bq.execute(sql_file.sql).to_dataframe().

I added a possible solution to this in an answer, if it was helpful to you please let me know. — Iñigo
– Iñigo, Commented Aug 21, 2018 at 12:51
@Iñigo very helpful thank you! Do you know if I need to go to the URL of the script if theSQL script and the python script are in the same repository? — Matt Zerweck
– Matt Zerweck, Commented Aug 21, 2018 at 22:02
It has to be stored by itself, imagine it as a variable where you only need the SQL script. I'm glad it helped, please consider accept/upvote my answer. Good luck! — Iñigo
– Iñigo, Commented Aug 22, 2018 at 7:08

Iñigo · Accepted Answer · 2018-08-25 15:14:58Z

4

You could use the "raw" page of that Github snippet containing the SQL script and check that page to get the script.

For example, let's say I want this script [1] (in your case it would be the SQL script), I click in the "RAW" button and save the URL. Then you can saw what it's inside that URL in Python by using requests:

import requests

raw=<URL OF YOUR SQL SCRIPT>
#In my case it would be
#raw="https://raw.githubusercontent.com/GoogleCloudPlatform/python-docs-samples/master/appengine/standard/bigquery/main.py"
r=requests.get(raw).text
df=bq.Query(r).to_dataframe()

If I understood correctly, that's what you want :D.

edited Aug 25, 2018 at 15:14

answered Aug 21, 2018 at 11:42

Iñigo

2,6902 gold badges12 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Google BigQuery Execute SQL file from Python Script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related