Big query EXPORT DATA statement creating mutiple files with no data and just header record

user_15113029 0
Viewed 6094

I have read similar issue here but not able to understand if this is fixed.

Google bigquery export table to multiple files in Google Cloud storage and sometimes one single file

I am using below big query EXPORT DATA OPTIONS to export the data from 2 tables in a file. I have written select query for the same.

EXPORT DATA OPTIONS(
uri='gs://whr-asia-datalake-dev-standard/outbound/Adobe/Customer_Master_'||CURRENT_DATE()||'*.csv',
format='CSV',
overwrite=true,
header=true,
field_delimiter='|') AS     
SELECT

I have only 2 rows returning from my select query and I assume that only one file should be getting created in google cloud storage. Multiple files are created only when data is more than 1 GB. thats what I understand.

However, 3 files got created in cloud storage where 2 files just had the header record and the third file has 3 records(one header and 2 actual data record)

radhika_sharma_ibm@cloudshell:~ (whr-asia-datalake-nonprod)$ gsutil ls gs://whr-asia-datalake-dev-standard/outbound/Adobe/
gs://whr-asia-datalake-dev-standard/outbound/Adobe/
gs://whr-asia-datalake-dev-standard/outbound/Adobe/Customer_Master_2021-02-04000000000000.csv
gs://whr-asia-datalake-dev-standard/outbound/Adobe/Customer_Master_2021-02-04000000000001.csv
gs://whr-asia-datalake-dev-standard/outbound/Adobe/Customer_Master_2021-02-04000000000002.csv

Why empty files are getting created? Can anyone please help? We don't want to create empty files. I believe only one file should be created when it is 1 GB. more than 1 GB, we should have multiple files but NOT empty.

google-bigquery google-cloud-platform
5 Answers

You have to force all data to be loaded into one worker. In this way you will be exporting only one file (if <1Gb). My workaround: add a select distinct * on top of the Select statement.

user_150514120

Under the hood, BigQuery utilizes multiple workers to read and process different sections of data and when we use wildcards, each worker would create a separate output file.

Currently BigQuery produces empty files even if no data is returned and thus we get multiple empty files. The Bigquery product team is aware of this issue and they are working to fix this, however there is no ETA which can be shared.

There is a public issue tracker that will be updated with periodic progress. You can STAR the issue to receive automatic updates and give it traction by referring to this link.

However for the time being I would like to provide a workaround as follows:

If you know that the output will be less than 1GB, you can specify a single URI to get a single output file. However, the EXPORT DATA statement doesn’t support Single URI.

You can use the bq extract command to export the BQ table.

bq --location=location extract \
--destination_format format \
--compression compression_type \
--field_delimiter delimiter \
--print_header=boolean \
project_id:dataset.table \
gs://bucket/filename.ext

In fact bq extract should not have the empty file issue like the EXPORT DATA statement even when you use Wildcard URI.

user_157504730

I faced the same empty files issue when using EXPORT DATA.

After doing a bit of R&D found the solution. Put LIMIT xxx in your SELECT SQL and it will do the trick.

You can find the count, and put that as LIMIT value.

SELECT ....

FROM ...

WHERE ...

LIMIT xxx

user_174490880
user_2437820

Specifying a wildcard seems to start several workers to work on the extract, and as per the documentation, size of the exported files will vary.

Zero-length files is unusual but technically possible if the first worker is done before any other really get started. Hence why the wildcard is expected to be used only when you think your exported data will be larger than the 1 GB

I have just faced the same with Parquet but found out that bq CLI works, which should do for any format.

See (and star for traction) https://issuetracker.google.com/u/1/issues/181016197

user_65113610
Related