BigQuery通过查询获取表模式 [英] BigQuery get table schema via query

问题描述

是否可以通过查询获取BigQuery表模式信息？ （类似于 TABLES ，但是用于模式）。

Is it possible to get BigQuery table schema info via query? (Something like TABLES but for schema). Not interested in cli approach.

我试图实现的是在2个BQ表（让我们说分期和制作）之间进行增量更新这个结构基于1个key列，但是有一种自动方式来创建密钥，其余的哈希基于所有其他列。

What I'm trying to achieve is to perform an "incremental" update between 2 BQ tables (let`s say staging and production) with the same structure based on 1 "key" column, but with an automatic way to create the key and the rest of the hash based on all other columns.

在MySQL / mariadb会是这样的。根据COLUMN_TYPE =PRI获取来自information_schema.columns的特定表中的列和字符串agg所有列，并保留为关键字，然后使用字符串agg获取所有其他列。这将自动生成一个SQL查询，以完成其余的工作。

In MySQL/mariadb would be something like. Get the columns from information_schema.columns for the specific table and string agg all columns based with COLUMN_TYPE = "PRI" and keep is as key, and then get all the others with a string agg also. This would auto generate a SQL query in order to do the rest stuff.

所以开始的问题是，如果有什么与MySQL的 information_schema有关的东西。 COLUMNS

So the starting question is if there is something relevant to MySQL's information_schema.COLUMNS

推荐答案

我可以看到一些方法，尽管它们与MYSQL方法不同 - / p>

I can see a couple of approaches, though they differ from the MYSQL approach -

1. 如果您有使用查询，只需创建一个装饰器来包装带有 limit 1 子句的查询。尽管您将收取与运行全表扫描相同的金额，但它会在响应中为您提供架构（请参阅查询作业的文档）。由于可能收取的费用不是最好的方法。


2. 如果您没有绑定到查询作业，为什么不使用表格获取方法？它返回一个包含模式的表资源对象

1. If you have to use a query, just create a decorator to wrap a query with a limit 1 clause. While you will be charged the same amount as running a full table scan, it will give you the the schema in the response (see documentation of the query job). Not the best approach due the possibly charges it might incur.
2. If you aren't tied to the query job, why not use the tables get method? It returns a table resource object which contains the schema

这篇关于BigQuery通过查询获取表模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！