查询GBQ中的列标题 [英] Querying Column Headers in GBQ

问题描述

是否可以执行查询以向我提供包含特定表的列标题的输出?我正在通过GBQ将多个文件上传到我们的服务器中，虽然它会自动检测标头，但我想以行或逗号分隔的单元格列出标头.

Is it possible to do a query to provide me an output with the column headers of a specific table? I'm uploading multiple files into our server via GBQ and while it auto-detects the headers, I would like to list out the headers either in rows or as a comma separated cell.

谢谢

推荐答案

我假设您的文件为CSV格式，因此表的架构中没有重复的字段.考虑到这一点-以下是适用于BigQuery Standard SQL的查询，只需要完全限定的表名

I am assuming your files are in CSV format so schema of table does not have repeated fields. With this in mind - below is for BigQuery Standard SQL and requires just fully qualified table name

#standardSQL
SELECT 
  REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"(.+?)"') cols_as_array,
  ARRAY_TO_STRING(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"(.+?)"'), ',') cols_as_string
FROM (SELECT 1) LEFT JOIN 
  (SELECT * FROM `project.dataset.table` WHERE FALSE) t
ON TRUE  

如果要应用于某些实际表(如以下示例所示)

If to apply to some real table as in below example

#standardSQL
SELECT 
  REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"(.+?)"') cols_as_array,
  ARRAY_TO_STRING(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"(.+?)"'), ',') cols_as_string
FROM (SELECT 1) LEFT JOIN 
  (SELECT * FROM `bigquery-public-data.utility_us.us_states_area` WHERE FALSE) t
ON TRUE  

结果将是

Row cols_as_array           cols_as_string   
1   region_code             region_code,division_code,state_fips_code,state_gnis_code,state_geo_id,state_abbreviation,state_name,legal_area_code,feature_class_code,functional_status_code,area_land_meters,area_water_meters,internal_point_lat,internal_point_lon,state_geom   
    division_code        
    state_fips_code      
    state_gnis_code      
    state_geo_id         
    state_abbreviation       
    state_name       
    legal_area_code      
    feature_class_code       
    functional_status_code       
    area_land_meters         
    area_water_meters        
    internal_point_lat       
    internal_point_lon       
    state_geom    

您可以选择要使用的版本:以数组形式列出或以逗号分隔的字符串形式列出

You can choose which version to use: list as array or list as comma separated string

还请注意，以上查询完全不会产生任何费用！

Also note, above query does not incur any cost at all!

这篇关于查询GBQ中的列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！