BigQuery: Export data into hierarchical folders: YYYY/MM/DD

Would a bash script with bq extract work?

#!/bin/bash

# Stop on first error
set -e;

# Used for Bigquery partitioning (to distinguish from bash variable reference)
DOLLAR="\$"

# -I ISO DATE
# -d FROM STRING
start=$(date -I -d 2019-06-01) || exit -1
end=$(date -I -d 2019-06-15)   || exit -1

d=${start}

# string(d) <= string(end)
while [[ ! "$d" > "$end" ]]; do
    YYYYMMDD=$(date -d ${d} +"%Y%m%d")
    YYYY=$(date -d ${d} +"%Y")
    MM=$(date -d ${d} +"%m")
    DD=$(date -d ${d} +"%d")

    # print current date
    echo ${d}

    cmd="bq extract --destination_format=AVRO \
    'project:dataset.table${DOLLAR}${YYYYMMDD}' \
    'gs://my-bucket/${YYYY}/${MM}/${DD}/part*.avro'
    "

    # execute    
    eval ${cmd}

    # d++
    d=$(date -I -d "$d + 1 day")
done

Maybe you should request a new feature at https://issuetracker.google.com/savedsearches/559654.

Not a bash ninja, so sure that there is a cooler way to compare dates.

At @Ben P's request here's the solution (a python script) I've used previously to run lots of export jobs in parallel. This is pretty rough code and should be improved by checking the status of each export job after it runs to see whether it succeeded.

I won't accept this an an answer because the question is looking for a bigquery-native way of performing this task.

Note that this script was for exporting a versioned dataset, so there's a bit of extra logic around that which many users may not need. It assumes that the input table and output folder names both use the version. This should be easy to strip out.

import argparse
import datetime as dt
from google.cloud import bigquery
from multiprocessing import Pool
import random
import time

GCS_EXPORT_BUCKET = "YOUR_BUCKET_HERE"
VERSION = "dataset_v1"

def export_date(export_dt, bucket=GCS_EXPORT_BUCKET, version=VERSION):
    table_id = '{}${:%Y%m%d}'.format(version, export_dt)
    gcs_filename = '{}/{:%Y/%m/%d}/{}-*.jsonlines.gz'.format(version, export_dt, table_id)
    gcs_path = 'gs://{}/{}'.format(bucket, gcs_filename)
    job_id = export_data_to_gcs(table_id, gcs_path, 'currents')
    return (export_dt, job_id)

def export_data_to_gcs(table_id, destination_gcs_path, dataset):
    bigquery_client = bigquery.Client()
    dataset_ref = bigquery_client.dataset(dataset)
    table_ref = dataset_ref.table(table_id)
    job_config = bigquery.job.ExtractJobConfig()
    job_config.destination_format = 'NEWLINE_DELIMITED_JSON'
    job_config.compression = 'GZIP'
    job_id = 'export-{}-{:%Y%m%d%H%M%S}'.format(table_id.replace('$', '--'),
                                                dt.datetime.utcnow())
    # Add a bit of jitter
    time.sleep(5 * random.random())
    job = bigquery_client.extract_table(table_ref,
                                        destination_gcs_path,
                                        job_config=job_config,
                                        job_id=job_id)
    print(f'Now running job_id {job_id}')
    time.sleep(50)
    job.reload()
    while job.running():
        time.sleep(10)
        job.reload()
    return job_id



if __name__ == "__main__":
    parser = argparse.ArgumentParser()

    parser.add_argument('-s', "--startdate",
                        help="The Start Date - format YYYY-MM-DD (Inclusive)",
                        required=True,
                        type=dt.date.fromisoformat)
    parser.add_argument('-e', "--enddate",
                        help="The End Date format YYYY-MM-DD (Exclusive)",
                        required=True,
                        type=dt.date.fromisoformat)

    args = parser.parse_args()

    start_date = args.startdate
    end_date = args.enddate

    dates = []
    while start_date < end_date:
        dates.append(start_date)
        start_date += dt.timedelta(days=1)

    with Pool(processes=30) as pool:
        jobs = pool.map(export_date, dates, chunksize=1)

To run this code, put it into a file called bq_exporter.py and then run python bq_exporter.py -s 2019-01-01 -e 2019-02-01. That'll export January of 2019, and print each of the export job's IDs. You can check on the status of a job using BigQuery CLI via bq show -j JOB_ID.