CODEWITHSUNDEEP
shellgoogle-bigqueryteradata
PacMan
PacMan

Reputation: 1358

Get total number of rows selected or updated in bigquery with bq command

I am trying to migrate some TERADATA features to BigQuery and I've been looking for something similar to the ACTIVITYCOUNT in TERADATA since I'm working on shell script environment, here is an example :

SELECT username, password from USERS_TABLE where true 
. IF ACTIVITYCOUNT=0 THEN .QUIT 122;

and here is the new version I've did to transform the SQL query into BigQuery

    bq query --format=csv --use_legacy_sql=false "SELECT username, password from USERS_TABLE where true" > output.txt
    ### How to get the number of rows selected ???

But I'm still struggling with the second instruction, I don't know if there is any native way to get the number of rows returned without doing a new sql query on the count cause I have a bunch of request to do the same thing on.

Upvotes: 0

Views: 1333

Answers (2)

Soumendra Mishra
Soumendra Mishra

Reputation: 3663

This is a workaround solution:

Select Statement:

$ bq query --nouse_legacy_sql 'SELECT * FROM `project.dataset.table`' | expr `wc -l` - 4 | sed 's/-[0-9]*/0/'

Update Statement:

$ bq query --nouse_legacy_sql 'UPDATE `project.dataset.table` SET sal = sal * 2 WHERE true' 2>/dev/null | awk '{print $NF}'

Upvotes: 1

Yun Zhang
Yun Zhang

Reputation: 5518

There could be various solutions, let me start with an easier one to see if it works for you.

(Harder one is to fetch results from a finished job)

ASSERT (SELECT COUNT(username) > 0 from USERS_TABLE where true) AS "USERS_TABLE cannot be empty";

Then your shell script only need to monitor if the query has an error or not.

See more on ASSERT: https://cloud.google.com/bigquery/docs/reference/standard-sql/debugging-statements

===========

One step further, you can see that it may be not necessary to break statements into smaller pieces to be sent by shell script. You can write a SQL script which asserts in various places already with Scripting

Upvotes: 0

Related Questions