Skip to content

Commit bc8b4b9

Browse files
alixhamialixhami
authored
Adds BigQuery authorized view samples (googleapis#5515)
1 parent 4ec0480 commit bc8b4b9

2 files changed

Lines changed: 123 additions & 5 deletions

File tree

‎bigquery/google/cloud/bigquery/table.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -607,7 +607,7 @@ def view_query(self):
607607
to :data:`None`).
608608
609609
By default, the query is treated as Standard SQL. To use Legacy
610-
SQL, set view_use_legacy_sql to True.
610+
SQL, set :attr:`view_use_legacy_sql` to :data:`True`.
611611
612612
Raises:
613613
ValueError: For invalid value types.

‎docs/bigquery/snippets.py‎

Lines changed: 122 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1072,6 +1072,124 @@ def test_browse_table_data(client, to_delete, capsys):
10721072
assert len(out) == 11
10731073

10741074

1075+
def test_manage_views(client, to_delete):
1076+
project = client.project
1077+
source_dataset_id = 'source_dataset_{}'.format(_millis())
1078+
source_dataset_ref = client.dataset(source_dataset_id)
1079+
source_dataset = bigquery.Dataset(source_dataset_ref)
1080+
source_dataset = client.create_dataset(source_dataset)
1081+
to_delete.append(source_dataset)
1082+
1083+
job_config = bigquery.LoadJobConfig()
1084+
job_config.schema = [
1085+
bigquery.SchemaField('name', 'STRING'),
1086+
bigquery.SchemaField('post_abbr', 'STRING')
1087+
]
1088+
job_config.skip_leading_rows = 1
1089+
uri = 'gs://cloud-samples-data/bigquery/us-states/us-states.csv'
1090+
source_table_id = 'us_states'
1091+
load_job = client.load_table_from_uri(
1092+
uri, source_dataset.table(source_table_id), job_config=job_config)
1093+
load_job.result()
1094+
1095+
shared_dataset_id = 'shared_dataset_{}'.format(_millis())
1096+
shared_dataset_ref = client.dataset(shared_dataset_id)
1097+
shared_dataset = bigquery.Dataset(shared_dataset_ref)
1098+
shared_dataset = client.create_dataset(shared_dataset)
1099+
to_delete.append(shared_dataset)
1100+
1101+
# [START bigquery_create_view]
1102+
# from google.cloud import bigquery
1103+
# client = bigquery.Client()
1104+
# project = 'my-project'
1105+
# source_dataset_ref = client.dataset('my_source_dataset')
1106+
# shared_dataset_ref = client.dataset('my_shared_dataset')
1107+
1108+
# This example shows how to create a shared view of a source table of
1109+
# US States. The source table contains all 50 states, while the view will
1110+
# contain only states with names starting with 'W'.
1111+
view_ref = shared_dataset_ref.table('my_shared_view')
1112+
view = bigquery.Table(view_ref)
1113+
sql_template = (
1114+
'SELECT name, post_abbr FROM `{}.{}.{}` WHERE name LIKE "W%"')
1115+
view.view_query = sql_template.format(
1116+
project, source_dataset_id, source_table_id)
1117+
view = client.create_table(view) # API request
1118+
1119+
print('Successfully created view at {}'.format(view.full_table_id))
1120+
# [END bigquery_create_view]
1121+
1122+
# [START bigquery_update_view_query]
1123+
# from google.cloud import bigquery
1124+
# client = bigquery.Client()
1125+
# project = 'my-project'
1126+
# source_dataset_ref = client.dataset('my_source_dataset')
1127+
# shared_dataset_ref = client.dataset('my_shared_dataset')
1128+
1129+
# This example shows how to update a shared view of a source table of
1130+
# US States. The view's query will be updated to contain only states with
1131+
# names starting with 'M'.
1132+
view_ref = shared_dataset_ref.table('my_shared_view')
1133+
view = bigquery.Table(view_ref)
1134+
sql_template = (
1135+
'SELECT name, post_abbr FROM `{}.{}.{}` WHERE name LIKE "M%"')
1136+
view.view_query = sql_template.format(
1137+
project, source_dataset_id, source_table_id)
1138+
view = client.update_table(view, ['view_query']) # API request
1139+
# [END bigquery_update_view_query]
1140+
1141+
# [START bigquery_get_view]
1142+
# from google.cloud import bigquery
1143+
# client = bigquery.Client()
1144+
# shared_dataset_id = 'my_shared_dataset'
1145+
1146+
view_ref = client.dataset(shared_dataset_id).table('my_shared_view')
1147+
view = client.get_table(view_ref) # API Request
1148+
1149+
# Display view properties
1150+
print('View at {}'.format(view.full_table_id))
1151+
print('View Query:\n{}'.format(view.view_query))
1152+
# [END bigquery_get_view]
1153+
assert view.view_query is not None
1154+
1155+
analyst_group_email = 'example-analyst-group@google.com'
1156+
# [START bigquery_grant_view_access]
1157+
# from google.cloud import bigquery
1158+
# client = bigquery.Client()
1159+
1160+
# Assign access controls to the dataset containing the view
1161+
# shared_dataset_id = 'my_shared_dataset'
1162+
# analyst_group_email = 'data_analysts@example.com'
1163+
shared_dataset = client.get_dataset(
1164+
client.dataset(shared_dataset_id)) # API request
1165+
access_entries = shared_dataset.access_entries
1166+
access_entries.append(
1167+
bigquery.AccessEntry('READER', 'groupByEmail', analyst_group_email)
1168+
)
1169+
shared_dataset.access_entries = access_entries
1170+
shared_dataset = client.update_dataset(
1171+
shared_dataset, ['access_entries']) # API request
1172+
1173+
# Authorize the view to access the source dataset
1174+
# project = 'my-project'
1175+
# source_dataset_id = 'my_source_dataset'
1176+
source_dataset = client.get_dataset(
1177+
client.dataset(source_dataset_id)) # API request
1178+
view_reference = {
1179+
'projectId': project,
1180+
'datasetId': shared_dataset_id,
1181+
'tableId': 'my_shared_view',
1182+
}
1183+
access_entries = source_dataset.access_entries
1184+
access_entries.append(
1185+
bigquery.AccessEntry(None, 'view', view_reference)
1186+
)
1187+
source_dataset.access_entries = access_entries
1188+
source_dataset = client.update_dataset(
1189+
source_dataset, ['access_entries']) # API request
1190+
# [END bigquery_grant_view_access]
1191+
1192+
10751193
def test_table_insert_rows(client, to_delete):
10761194
"""Insert / fetch table data."""
10771195
dataset_id = 'table_insert_rows_dataset_{}'.format(_millis())
@@ -2648,7 +2766,7 @@ def test_query_external_gcs_temporary_table(client):
26482766
job_config.table_definitions = {table_id: external_config}
26492767

26502768
# Example query to find states starting with 'W'
2651-
sql = 'SELECT * FROM {} WHERE name like "W%"'.format(table_id)
2769+
sql = 'SELECT * FROM `{}` WHERE name LIKE "W%"'.format(table_id)
26522770

26532771
query_job = client.query(sql, job_config=job_config) # API request
26542772

@@ -2689,7 +2807,7 @@ def test_query_external_gcs_permanent_table(client, to_delete):
26892807
table = client.create_table(table) # API request
26902808

26912809
# Example query to find states starting with 'W'
2692-
sql = 'SELECT * FROM {}.{} WHERE name like "W%"'.format(
2810+
sql = 'SELECT * FROM `{}.{}` WHERE name LIKE "W%"'.format(
26932811
dataset_id, table_id)
26942812

26952813
query_job = client.query(sql) # API request
@@ -2734,7 +2852,7 @@ def test_query_external_sheets_temporary_table(client):
27342852
job_config.table_definitions = {table_id: external_config}
27352853

27362854
# Example query to find states starting with 'W'
2737-
sql = 'SELECT * FROM {} WHERE name like "W%"'.format(table_id)
2855+
sql = 'SELECT * FROM `{}` WHERE name LIKE "W%"'.format(table_id)
27382856

27392857
query_job = client.query(sql, job_config=job_config) # API request
27402858

@@ -2786,7 +2904,7 @@ def test_query_external_sheets_permanent_table(client, to_delete):
27862904
table = client.create_table(table) # API request
27872905

27882906
# Example query to find states starting with 'W'
2789-
sql = 'SELECT * FROM {}.{} WHERE name like "W%"'.format(
2907+
sql = 'SELECT * FROM `{}.{}` WHERE name LIKE "W%"'.format(
27902908
dataset_id, table_id)
27912909

27922910
query_job = client.query(sql) # API request

0 commit comments

Comments
 (0)