8

I have around 108 tables in a dataset. I am trying to extract all those tables using the following bash script:

# get list of tables
tables=$(bq ls "$project:$dataset" | awk '{print $1}' | tail +3)

# extract into storage
for table in $tables
do
    bq extract --destination_format "NEWLINE_DELIMITED_JSON" --compression "GZIP" "$project:$dataset.$table" "gs://$bucket/$dataset/$table.json.gz" 
done

But it seems that bq ls only show around 50 tables at once and as a result I can not extract them to cloud storage.

Is there anyway I can access all of the 108 tables using the bq ls command?

Syed Arefinul Haque
  • 1,123
  • 2
  • 14
  • 38

3 Answers3

14

I tried with CLI and This command worked for me:-

bq ls --max_results 1000 'project_id:dataset'

Note: --max_results number_based_on_Table_count

Hoppo
  • 1,130
  • 1
  • 13
  • 32
Kumar Pankaj Dubey
  • 1,541
  • 3
  • 17
  • 17
11

The default number of rows when listing tables that bq ls will display is 100. You can change this with the command line option --max_results or -n.

You can also set the default values for bq in $HOME/.bigqueryrc.

Adding flags to .bigqueryrc

John Hanley
  • 74,467
  • 6
  • 95
  • 159
0

This will take all the views and m/views for your dataset, and push them into a file.

Could add another loop to loop through all datasets too

#!/bin/bash

## THIS shell script will pull down every views SQL in a dataset into its own file

# Get the project ID and dataset name
DATASET=<YOUR_DATASET>

# for dataset in $(bq ls --format=json | jq -r '.[] | .dataset_id'); do
    # Loop over each table in the dataset
    for table in $(bq ls --max_results 1000 "$PROJECT_ID:$DATASET" | tail -n +3 | awk '{print $1}'); do

        # Determine the table type and file extension
        if bq show --format=prettyjson $DATASET.$table | jq -r '.type' | grep -q -E "MATERIALIZED_VIEW|VIEW"; then
            file_extension=".bqsql"
                # Output the table being processed
            echo "Extracting schema for $DATASET.$table"

            # Get the schema for the table
            bq show --view --format=prettyjson $DATASET.$table | jq -r '.view.query' > "$DATASET-$table.$file_extension"
        else
            echo "Ignoring $table"
            continue
        fi
    done
# done
mewc
  • 1,253
  • 1
  • 15
  • 24