CODEWITHSUNDEEP
google-bigquery
codninja0908
codninja0908

Reputation: 567

Extract metadata_fields from table as well as data columns from bigquery table

I have to query a bigquery table where in the requirement is to fetch below fields:

a) Table metadata columns( 2 columns)

b) Data columns( 3 columns)

for example :

table_id ,   creation_time , col_id1 , col2_id2, col3_id3
tbl_20180424, 1524641477022,  1, 2, 3
tbl_20180524, 1524647897022,  11, 12, 13

I tried below query but it is not working:

SELECT
table_id,
creation_time,
year,
Week,
id1,
id2,
id3
FROM (
SELECT  
table_id,
TIMESTAMP_MILLIS(creation_time) AS creation_time,
EXTRACT(YEAR FROM CAST(TIMESTAMP_MILLIS(creation_time) as DATE)) Year,
EXTRACT(WEEK FROM CAST(TIMESTAMP_MILLIS(creation_time) as DATE)) Week
FROM `project.dataset.__TABLES_SUMMARY__`
where table_id like 'tbl_%' )

Upvotes: 0

Views: 134

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below is for BigQuery Standard SQL 

#standardSQL
WITH data AS (
  SELECT CONCAT('tbl_', _TABLE_SUFFIX) AS table_id, col_id1, col_id2, col_id3
  FROM `project.dataset.tbl_*`
), metadata AS (
  SELECT 
    table_id,
    TIMESTAMP_MILLIS(creation_time) AS creation_time,
    EXTRACT(YEAR FROM CAST(TIMESTAMP_MILLIS(creation_time) AS DATE)) Year,
    EXTRACT(WEEK FROM CAST(TIMESTAMP_MILLIS(creation_time) AS DATE)) Week
  FROM `project.dataset.__TABLES_SUMMARY__`
  WHERE table_id LIKE 'tbl_%'
)
SELECT *
FROM metadata
JOIN data
USING(table_id)

if to apply to simplified example in your question - result will be 

Row table_id        creation_time               Year    Week    col_id1 col_id2 col_id3  
1   tbl_20180424    2019-09-20 19:21:38.600 UTC 2019    37      1       2       3    
2   tbl_20180524    2019-09-20 19:22:00.676 UTC 2019    37      11      12      13

Upvotes: 2

rtenha
rtenha

Reputation: 3616

In your query, you are trying to select id1, id2, id3 but they are not in your subquery. Additionally, consider looking into INFORMATION_SCHEMA as follows:

with tables as (select table_name, creation_time from dataset.INFORMATION_SCHEMA.TABLES),
    columns as (select table_name, column_name, ordinal_position from dataset.INFORMATION_SCHEMA.COLUMNS)
select
  table_name,
  creation_time,
  EXTRACT(YEAR from creation_time) as Year,
  EXTRACT(WEEK from creation_time) as Week,
  column_name,
  ordinal_position
from tables
inner join columns using(table_name)
order by table_name, ordinal_position

This query provides an un-pivoted result set of all tables/columns in your dataset.

Upvotes: 0

Related Questions