BigQuery - Export query results to local file/Google storage

Question

I want to export query results from BigQuery to local file/Google storage.

I tried 'bq extract' command but it doesn't allow query as input.

Usage: bq extract <source_table> <destination_uris>

I don't want to extract the whole table as the table contains many columns which are not required and I need to aggregate the data.

As of now, the only workaround I could find is to create a table using the 'bq query' command and use the 'bq extract' to extract the data.

I'm looking for any better way to achieve this by doing something like below.

bq extract 'select dept_id,sum(sal) from temp.employee 
                                 group by dept_id' 'gs://XXXX/employee.csv'

Answer 1

Direct export from BigQuery Standard SQL was added recently: Exporting data to csv format

EXPORT DATA OPTIONS(
  uri='gs://mybucket/myfolder2/*.csv',
  format='CSV',
  overwrite=true,
  header=true,
  field_delimiter=';') AS
SELECT 1 as field1, 2 as field2