How to select all columns names that contain a substring in BigQuery?

user_7991624 0
Viewed 2604

I would like to select all of the columns that have a column name that contains the substring 'age_' 

 id   gender   age_now   age_graduated
 aa     F        21         25 
 bb     M        34         24

I only want columns 'age_now' and 'age_graduated'

google-bigquery sql
2 Answers

If you don't mind using some Python to generate your queries:

from google.cloud import bigquery

client = bigquery.Client(project=project_id)
table = client.get_table(
    client.dataset('public_dump', project='fh-bigquery').table('test201602')) 
pattern_cols = [x.name for x in table.schema if x.name.startswith("inferred")]
print ( "SELECT %s\nFROM `%s.%s.%s`" % (
    ','.join(pattern_cols), table.project, table.dataset_id, table.table_id))

enter image description here

user_1324380

If you know columns that are not age_ prefixed you could use SELECT * EXCEPT:

SELECT * EXCEPT id, gender
FROM tab;

AFAIK there is no syntax like:

SELECT * LIKE 'age_%'
FROM tab

So the only solution is to read metadata INFORMATION_SCHEMA.COLUMNS table and build dynamic SQL query.

user_50708790
Related