Skip to content

Big Query

Jump to bottom
Bill Hereth edited this page Apr 22, 2024 · 1 revision

Connecting in Python/Jupyter

A google doc explains how to connect to BigQuery: Access BigQuery Streetlight through Local Jupyter Notebook

StreetLight Data Example

#google cloud big query libaries
from google.cloud import bigquery
from google.oauth2 import service_account

key_path = r"C:\Users\bhereth\streetlight-temp-analysis-e2b201d26862.json"

credentials = service_account.Credentials.from_service_account_file(
    key_path, scopes=["https://www.googleapis.com/auth/cloud-platform"],
)

client = bigquery.Client(credentials=credentials, project=credentials.project_id,)

#ease of use
daytype0 = '0: All Days (Mo-Su)'
daytype1 = '1: Weekday (Tu-Th)'
daytype2 = '2: Weekend Day (Sa-Su)'
dataper1 = '1. All year'
dataper2 = '2. Sep-Nov'
dataper3 = '3. Dec-Feb'
dataper4 = '4. Mar-May'
dataper5 = '5. Jun-Aug'
daypart0 = '0: All Day (12am-12am)'
daypart1 = '1: Early AM (12am-6am)'
daypart2 = '2: Peak AM (6am-9am)'
daypart3 = '3: Mid-Day (9am-3pm)'
daypart4 = '4: Peak PM (3pm-6pm)'
daypart5 = '5: Late PM (6pm-12am)'

#zones for interzonal definition
dImportTables = [
                    ['ut-udot-adap-dev.streetlight_data.udot_commercial_all_year_2019'],
                    ['ut-udot-adap-dev.streetlight_data.udot_commercial_fall_2019'    ],
                    ['ut-udot-adap-dev.streetlight_data.udot_commercial_spring_2019'  ],
                    ['ut-udot-adap-dev.streetlight_data.udot_commercial_summer_2019'  ],
                    ['ut-udot-adap-dev.streetlight_data.udot_commercial_winter_2019'  ]
                ]

sJoinTable = "ut-udot-adap-dev.streetlight_data.wfrc_streetlight_taz"


#create query of all subareaid to subareaid truck flows using join to taz table with subareaid field
dfTruckDataBySubareaID = pd.DataFrame()

for dit in dImportTables:
    sql = """
    SELECT
        orig_table.SUBAREAID AS origin_subareaid,
        dest_table.SUBAREAID AS destination_subareaid,
        od_table.mode_of_travel AS mode_of_travel,
        od_table.vehicle_weight AS vehicle_weight,
        od_table.day_type AS day_type,
        od_table.day_part AS day_part,
        od_table.data_period AS data_period,
        SUM(od_table.o_d_traffic_sample_trip_counts) AS od_trip_counts_sum,
        SUM(od_table.o_d_traffic_calibrated_trip_volume) AS od_trip_volume_sum,
        COUNT(mode_of_travel) AS num_records
    FROM
        (""" + dit[0] + """ as od_table
            LEFT JOIN
                """ + sJoinTable + """ AS orig_table
                ON od_table.origin_zone_name = orig_table.SL_COTAZID)
                    LEFT JOIN """ + sJoinTable + """ AS dest_table
                    ON od_table.destination_zone_name = dest_table.SL_COTAZID
    GROUP BY
        orig_table.SUBAREAID,
        dest_table.SUBAREAID,
        od_table.mode_of_travel,
        od_table.vehicle_weight,
        od_table.day_type,
        od_table.day_part,
        od_table.data_period
    """
    #display(sql)

    dfQuery = client.query(sql).to_dataframe()

Connecting via ODBC to MS Access (use similar method to connect to other DBS)

  • Download and install driver: https://cloud.google.com/bigquery/docs/reference/odbc-jdbc-drivers
  • Configure ODBC Connection
    • Open ODBC Data Sources Administrator (64-bit or 32-bit)
    • Click System DSN tab
    • Select Google BigQuery
    • Select Configure
    • Have BigQuery key email and file (as explained in Connecting in Python/Jupyter, above)
    • In email field, add email address associated with from key
    • In Key File Path enter json filename with key
    • In Catalog (Project) select Google Cloud project (dropdown should auto-populate based on key)
    • In Dataset select name of dataset (dropdown should auto-populate based on key)
    • Click Test and OK if works
  • In MS Access:
Clone this wiki locally