如何使用 python API 在 bigquery 中创建新视图? [英] How can I create a new view in bigquery using the python API?
问题描述
我有一些代码可以自动生成一堆不同的 SQL 查询，我想将这些查询插入到 bigquery 中以生成视图，尽管我遇到的问题之一是这些视图需要每晚动态生成，因为数据性质的变化.所以我希望能够做的是使用 google bigquery api for python 来创建视图.我了解如何使用bq"命令行工具执行此操作，但我希望能够将其直接内置到代码中，而不是使用 shell 来运行 bq.我玩过
I have some code that automatically generates a bunch of different SQL queries that I would like to insert into the bigquery to generate views, though one of the issues that I have is that these views need to be generated dynamically every night because of the changing nature of the data. So what I would like to be able to do is use the google bigquery api for python to be able to make a view. I understand how to do it using the 'bq' command line tool, but I'd like to be able to have this built directly into the code as opposed to using a shell to run bq. I have played with the code provided at
https://cloud.google.com/bigquery/bigquery-api-quickstart
我不明白如何使用这段代码来创建视图，而不仅仅是返回 SELECT 语句的结果.我可以在这里看到关于做表插入的文档
I don't understand how to use this bit of code to create a view instead of just returning the results of a SELECT statement. I can see the documentation about doing table inserts here
https://cloud.google.com/bigquery/docs/参考/v2/tables/insert
但这指的是使用 REST API 生成新表，而不是上面提供的示例.
but that refers to using the REST API to generate new tables as opposed to the example provided above.
难道就不可能吗?我应该放弃并使用 bq 吗?
Is it just not possible? Should I just give in and use bq?
谢谢
*** 针对 Felipe 评论的一些其他问题.
*** Some additional questions in response to Felipe's comments.
表格资源文档表明有许多必填字段，其中一些是有意义的，即使我不完全理解他们的要求，其他人则没有.例如，externalDataConfiguration.schema.这是指我要连接的数据库的架构(我假设是)，还是存储数据的架构?
The table resource document indicates that there are a number of required fields, some of which make sense even if I don't fully understand what they're asking for, others do not. For example, externalDataConfiguration.schema. Does this refer to the schema for the database that I'm connecting to (I assume it does), or the schema for storing the data?
externalDataConfiguration.sourceFormat 怎么样?由于我正在尝试查看预先存在的数据库，因此我不确定我是否了解源格式的相关性.它是我正在查看的数据库的源格式吗?我将如何识别?
What about externalDataConfiguration.sourceFormat? Since I'm trying to make a view of a pre-existing database, I'm not sure I understand how the source format is relevant. Is it the source format of the database I'm making a view from? How would I identify that?
和 externalDataConfiguration.sourceUris[]，我没有将新数据导入数据库，所以我不明白这个(或前一个元素)是如何需要的.
ANd externalDataConfiguration.sourceUris[], I'm not importing new data into the database, so I don't understand how this (or the previous element) are required.
架构呢?
tableReference.datasetId、tableReference.projectId 和 tableReference.tableId 是不言自明的.
tableReference.datasetId, tableReference.projectId, and tableReference.tableId are self explanatory.
类型将是视图，而 view.query 将是用于创建视图的实际 sql 查询.所以我明白为什么需要这些来制作视图，但我不明白其他部分.
Type would be view, and view.query would be the actual sql query used to make the view. So I get why those are required for making a view, but I don't understand the other parts.
你能帮我了解这些细节吗?
Can you help me understand these details?
谢谢，布拉德
推荐答案
Web UI 或 bq 工具所做的一切都是通过 BigQuery API 完成的，所以不要放弃:).
Everything that web UI or the bq tool does is made through the BigQuery API, so don't give up yet :).
创建视图类似于创建表，只需确保在调用tables.insert() 时有一个包含视图属性的表资源.
Creating a view is akin to creating a table, just be sure to have a table resource that contains a view property when you call tables.insert().
- https://cloud.google.com/bigquery/querying-data#views
- https://cloud.google.com/bigquery/docs/参考/v2/tables#resource
这篇关于如何使用 python API 在 bigquery 中创建新视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
