Adding Ad-Hoc Data to the Warehouse

Adding Ad-Hoc Data to the Warehouse

To work with data in our BI tool (Metabase) we first have to add the data to our warehouse (BigQuery). This page describes how to do an ad-hoc, one-time import of a dataset (for example, an individual extract from some other system.)

Warning

If you are working with a data source that needs to be updated regularly (like data that should be ingested/updated daily, weekly, or monthly), you should follow the general data pipeline architecture and create a dedicated pipeline. The instructions here are only for one-time imports of individual files that will not be regularly updated.

To add one-time data to BigQuery for use in Metabase follow the instructions below. You may need additional Google Cloud or BigQuery permissions; if so, post in the #services-team channel in Slack to ask for assistance.

  1. First, upload your data to a Google Cloud Storage bucket in the gs://calitp-analytics-data/data-analyses/ directory and make note of the gsutil URI (file path).

  2. Next, navigate to a JupyterLab terminal window.

  3. Once in the terminal, input the following command with the appropriate structure:

bq --location=us-west2 load <source_format> --autodetect <destination_table> <source>

  • The <source_format> specifies the type of file you would like to use. An example of this flag’s use is --source_format=CSV. Other options include PARQUET and NEWLINE_DELIMITED_JSON

  • The <destination_table> is the table you would like to create, or append to if the table already exists. Your uploaded table destination should always be the uploaded_data dataset in BigQuery (e.g. the destination_table name should always have the format uploaded_data.your_new_table_name).

    • If you are looking to create a new table: use a new table name

    • If you are looking to append to existing data: re-use the name of the existing table

    • If you are looking to replace an existing table: use the --replace flag after the load command

  • The <source> argument is the gsutil URI (the path to the Google Cloud Storage bucket you are sourcing from).

  • If you run into upload errors related to the source file format, you may need to include the flag --allow_quoted_newlines. This may be helpful in resolving errors related to newline-delimited text, which may be present in file conversions from Excel to CSV.

Ex.

bq --location=us-west2 load --source_format=CSV --autodetect --allow_quoted_newlines uploaded_data.tircp_with_temporary_expenditure_sol_copy gs://calitp-analytics-data/data-analyses/tircp/tircp.csv

Looking for more information?

More information on the BigQuery Command Line Interface (CLI) can be found here

previous

Developing models in dbt

next

What is GTFS, anyway?