python-2.7 - 大查询视图中的字段说明
问题描述
我知道这个问题之前已经提出并且似乎已经解决了.. https://issuetracker.google.com/issues/35905194
但这可以使用 Python 完成吗?
我有一个脚本,当我想更新 Big Query 表上的字段描述但当我在视图上运行完全相同的脚本时没有任何反应?
脚本运行,我没有收到任何错误,但它只是没有更新 Big Query 中的视图
我正在使用 Python 2.7.13
这是我的代码..
from google.cloud import bigquery
from datetime import datetime
import json
import sys
project='xxxx'
ds='xxxxx'
table_n='xxxxx'
startTime=datetime.now()
#Authorisation
filename='xxxxxx.json'
client = bigquery.Client.from_service_account_json(filename)
dataset_id = ds
table_id = table_n
table_schema= []
table_schema.append(bigquery.SchemaField('BWMI_ID', 'STRING', mode='NULLABLE'))
table_schema.append(bigquery.SchemaField('VP_VIN_PREFIX', 'STRING', mode='NULLABLE'))
table_schema.append(bigquery.SchemaField('CHASSIS_NUMBER', 'STRING', mode='NULLABLE'))
table_schema.append(bigquery.SchemaField('CAESAR_KEY', 'INTEGER', mode='NULLABLE'))
table_schema.append(bigquery.SchemaField('ID', 'INTEGER', mode='NULLABLE'))
table_schema.append(bigquery.SchemaField('VEH_ID', 'INTEGER', mode='NULLABLE'))
table_schema.append(bigquery.SchemaField('LOEV_ID', 'INTEGER', mode='NULLABLE'))
table_schema.append(bigquery.SchemaField('D42_LAST_UPDATED', 'TIMESTAMP', mode='NULLABLE'))
table_schema.append(bigquery.SchemaField('D42_END_DATE', 'TIMESTAMP', mode='NULLABLE'))
table_schema.append(bigquery.SchemaField('USER_1_ID', 'STRING', mode='NULLABLE'))
table_schema.append(bigquery.SchemaField('DESCRIPTION', 'STRING', mode='NULLABLE'))
table_schema.append(bigquery.SchemaField('DATE_ADDED', 'TIMESTAMP', mode='NULLABLE'))
table_schema.append(bigquery.SchemaField('DATE_ENDED', 'TIMESTAMP', mode='NULLABLE'))
table_schema.append(bigquery.SchemaField('SOURCE', 'STRING', mode='NULLABLE'))
table_schema.append(bigquery.SchemaField('CANCELLED', 'STRING', mode='NULLABLE'))
table_schema.append(bigquery.SchemaField('CANCELLED_DATE', 'TIMESTAMP', mode='NULLABLE'))
table_schema.append(bigquery.SchemaField('CRC32', 'STRING', mode='NULLABLE'))
table_schema.append(bigquery.SchemaField('CREATED_DATE', 'TIMESTAMP', mode='NULLABLE'))
original_schema=table_schema
table_ref = client.dataset(dataset_id).table(table_id)
table = bigquery.Table(table_ref, schema=original_schema)
new_schema= []
new_schema.append(bigquery.SchemaField('BWMI_ID', 'STRING', mode='NULLABLE', description = 'Updated by python script'))
new_schema.append(bigquery.SchemaField('VP_VIN_PREFIX', 'STRING', mode='NULLABLE', description = 'Updated by python script'))
new_schema.append(bigquery.SchemaField('CHASSIS_NUMBER', 'STRING', mode='NULLABLE', description = 'Updated by python script'))
new_schema.append(bigquery.SchemaField('CAESAR_KEY', 'INTEGER', mode='NULLABLE', description = 'Updated by python script'))
new_schema.append(bigquery.SchemaField('ID', 'INTEGER', mode='NULLABLE'))
new_schema.append(bigquery.SchemaField('VEH_ID', 'INTEGER', mode='NULLABLE'))
new_schema.append(bigquery.SchemaField('LOEV_ID', 'INTEGER', mode='NULLABLE', description = 'Updated by python script'))
new_schema.append(bigquery.SchemaField('D42_LAST_UPDATED', 'TIMESTAMP', mode='NULLABLE'))
new_schema.append(bigquery.SchemaField('D42_END_DATE', 'TIMESTAMP', mode='NULLABLE'))
new_schema.append(bigquery.SchemaField('USER_1_ID', 'STRING', mode='NULLABLE', description = 'Updated by python script'))
new_schema.append(bigquery.SchemaField('DESCRIPTION', 'STRING', mode='NULLABLE'))
new_schema.append(bigquery.SchemaField('DATE_ADDED', 'TIMESTAMP', mode='NULLABLE'))
new_schema.append(bigquery.SchemaField('DATE_ENDED', 'TIMESTAMP', mode='NULLABLE', description = 'Updated by python script'))
new_schema.append(bigquery.SchemaField('SOURCE', 'STRING', mode='NULLABLE'))
new_schema.append(bigquery.SchemaField('CANCELLED', 'STRING', mode='NULLABLE'))
new_schema.append(bigquery.SchemaField('CANCELLED_DATE', 'TIMESTAMP', mode='NULLABLE', description = 'Updated by python script'))
new_schema.append(bigquery.SchemaField('CRC32', 'STRING', mode='NULLABLE'))
new_schema.append(bigquery.SchemaField('CREATED_DATE', 'TIMESTAMP', mode='NULLABLE'))
table.schema = new_schema
table = client.update_table(table, ['schema'])
解决方案
我试图复制您的场景,但是,Python 客户端库对我来说运行良好
#NEW VIEW
view_ref = client.dataset(dataset_id).table(view_name)
client.delete_table(view_ref)
view = bigquery.Table(view_ref)
sql_template = ('SELECT DISTINCT user,team FROM `{}.{}.{}` WHERE score = 19')
view.view_query = sql_template.format(project,dataset_id,original_table)
view = client.create_table(view)
print('Successfully created view at {}'.format(view.full_table_id))
#UPDATE VIEW
view_schema= [
bigquery.SchemaField('user', 'STRING', mode='NULLABLE',description='user name VIEW updated'),
bigquery.SchemaField('team', 'STRING', mode='NULLABLE',description='team name VIEW updated'),
]
viewU = bigquery.Table(view_ref,schema=view_schema)
viewU = client.update_table(viewU,['schema'])
print('UPDATED VIEW SCHEMA: {}').format(viewU.schema) #VERIFY THE SCHEMA
以及bq 命令:
bq show --format=prettyjson [PROJECT_ID]:[DATASET].[VIEW]
您是否尝试过单击Refresh
BQ UI 中“查看详细信息”屏幕中的按钮?
推荐阅读
- javascript - 简单的 Javascript 删除类
- php - 移动 CakePHP 1.3.8 安装映像问题
- r - 如何将一定数量的行从大 JSON 加载到数据框中?
- reactjs - React 中的 JSX if/else 语法
- spring-boot - JHipster:我不能添加像“angular-2-dropdown-multiselect”这样的第三方依赖
- nginx - 将 LUA 模块添加到 nginx
- javascript - 为什么我在 load() jquery 函数中出现“XML Parsing Error: junk after document element”错误?
- data-mining - 使用 k-means 或 k-nn 从两个数据集可以推断出什么
- visual-studio-code - 在 vscode 扩展中监视文件夹创建
- java - 如何在活动之间传递 SQLDatabaseHelper 对象?