BigQuery

A few notes for Pandas and BigQuery

Get the memory size of a DataFrame of Pandas

df.memory_usage(deep=True).sum()

2. Upload a large DataFrame of Pandas to BigQuery table

If your DataFrame is too big, the uploading operation will report “UDF out of memory”

google.api_core.exceptions.BadRequest: 400 Resources exceeded during query execution: UDF out of memory.; Failed to read Parquet file [...]. This might happen if the file contains a row that is too large, or if the total size of the pages loaded for the queried columns is too large.

The solution is as simple as splitting the DataFrame and upload them one by one:

client = bigquery.Client()
for df_chunk in np.array_split(df, 10):
    job_config = bigquery.LoadJobConfig()
    job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND
    job = client.load_table_from_dataframe(df_chunk, table_id, job_config=job_config)
    job.result()

3. Restore table in BigQuery

How to recover a deleted table in BigQuery? Just use bq command

bq cp dataset.table@1577833205000 dataset.new_table

If your <timestamp> is not correct, the bq command will give you a notification about what <timestamp> is right for this table. Then you can use that correct <timestamp> again.

Import date column in Pandas to BigQuery

Imaging we have a small CSV file:

name,enroll_time
robin,2021-01-15 09:50:33
tony,2021-01-14 01:50:33
jaime,2021-01-13 00:50:33
tyrion,2021-2-15 13:22:17
bran,2022-3-16 14:00:01

Let’s try to load it into DataFrame of Pandas and upload it to a table of BigQuery:

import pandas as pd
from google.cloud import bigquery
df = pd.read_csv("test.csv", parse_dates=["enroll_time"], index_col=0)
schema = []
schema.append(bigquery.SchemaField("name", "STRING"))
schema.append(bigquery.SchemaField("enroll_time", "DATE"))
job_config = bigquery.LoadJobConfig(schema=schema)
bq_client = bigquery.Client()
table = "project.dataset.test_table"
job = bq_client.load_table_from_dataframe(
    df, table, job_config=job_config
)
job.result()

But it reports error:

  File "pyarrow/array.pxi", line 176, in pyarrow.lib.array
  File "pyarrow/array.pxi", line 85, in pyarrow.lib._ndarray_to_array
  File "pyarrow/error.pxi", line 81, in pyarrow.lib.check_status
pyarrow.lib.ArrowInvalid: Casting from timestamp[ns] to date32[day] would lose data: 1610704233000000000

Seems the BigQuery library couldn’t recognize the 1610704233000000000 as nano-seconds. Then I tried to divide the 1610704233000000000 with 1e9 but it also failed.

Actually what we need to do is just use TIMESTAMP instead of DATE as the type of column enroll_time:

schema.append(bigquery.SchemaField("name", "STRING"))
schema.append(bigquery.SchemaField("enroll_time", "TIMESTAMP"))

and the BigQuery library could recognize the column even with nano-seconds unit.

Compare two tables in BigQuery

As this answer, the best solution for comparing two tables in BigQuery is:

(
  SELECT * FROM table1
  EXCEPT DISTINCT
  SELECT * from table2
)
UNION ALL
(
  SELECT * FROM table2
  EXCEPT DISTINCT
  SELECT * from table1
)

But in my test, two tables with the same rows report difference by using the above snippet. Then I found out that the order of column names may be different, and the order of rows too. Then the better solution should be fixing the order of column names and rows:

(
  (
  SELECT col, col2, col3, col4
  FROM table1
  ORDER BY col1, col2
  )
  EXCEPT DISTINCT
  (
  SELECT col1, col2, col3, col4
  FROM table2
  ORDER BY
  col1, col2
  )
)
UNION ALL
(
  (
  SELECT col, col2, col3, col4
  FROM table2
  ORDER BY col1, col2
  )
  EXCEPT DISTINCT
  (
  SELECT col1, col2, col3, col4
  FROM table1
  ORDER BY
  col1, col2
  )
)

Transfer Redshift SQL to BigQuery SQL

In my recent work, I need to run some SQL snippet from Redshift on Google’s BigQuery platform. Since different data warehouses have a different recipe for SQL, the transferring work couldn’t be avoided.

Here comes some tricks:

Redshift	BigQuery
field::VARCHAR	CAST(field AS String)
isnull(), nvl()	ifnull()
dateadd()	date_add()
datediff()	date_diff()
union	union all
field ILIKE pattern	UPPER(field) LIKE pattern
split_part(string, delimiter, part)	split(string, delimiter)[safe_offset(part)]

In Redshift we can select columns like this:

SELECT
  SQRT(score) AS new_score,
  new_score * 10
FROM ...

But in BigQuery we couldn’t use column name from “AS”. The SQL in BigQuery should be:

SELECT
  SQRT(score) AS new_score,
  SQRT(score) * 10
FROM ...

And, BigQuery has the “WITH” clause to replace the “temporary table”, which is very powerful:

WITH result AS (
   WITH example AS ( SELECT * FROM `dataset.table` )
   SELECT * FROM example
)
SELECT * FROM result

Some tips about BigQuery on GCP

Migrate SQL script from AWS Redshift to BigQuery

CONVERT_TIMEZONE('AEDT',getdate())::DATE

in Redshift should be changed to

current_date("Australia/Sydney")

in BigQuery.
Since BigQuery doesn’t force type conversion, some NULL value in Redshift could be a NULL value or a ‘NULL’ string in BigQuery. Make sure you use both

column is NULL

and

column = 'NULL'

for checking.
In BigQuery, we can also use UDF like this:

create temp function change_date(the_date DATE, offset_day INT64) AS (
    DATE_ADD(the_date, INTERVAL offset_day DAY)
);
create temp function next_thursday(the_date DATE) AS (
    change_date(DATE_TRUNC(the_date, WEEK(THURSDAY)), 7)
);

Performance improvement of BigQuery SQL
Remove ‘DISTINCT’ in SQL and de-dup data later in Pandas could boost whole performance for data processing. Even ‘CAST’ in BigQuery would hurt the performance. The best way to find the bottlenecks for your SQL is by looking at the ‘Execution details‘ in GUI.
Loading speed
For pandas-gbq, we can accelerate the speed of reading BigQuery table by adding argument ‘use_bqstorage_api=True’ in ‘read_gbq()’ function:

df = pandas_gbq.read_gbq(bqsqlfile, project_id='myproject', use_bqstorage_api=True)

Robin on Linux

BigQuery