Skip to content

BigQuery: Adds authorized view samples #5515

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
alixhami merged 1 commit into from
Jun 21, 2018
Merged

BigQuery: Adds authorized view samples #5515

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
2 changes: 1 addition & 1 deletion bigquery/google/cloud/bigquery/table.py
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -607,7 +607,7 @@ def view_query(self):
to :data:`None`).

By default, the query is treated as Standard SQL. To use Legacy
SQL, set view_use_legacy_sql to True.
SQL, set :attr:`view_use_legacy_sql` to :data:`True`.

Raises:
ValueError: For invalid value types.
Expand Down
126 changes: 122 additions & 4 deletions docs/bigquery/snippets.py
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -1072,6 +1072,124 @@ def test_browse_table_data(client, to_delete, capsys):
assert len(out) == 11


def test_manage_views(client, to_delete):
project = client.project
source_dataset_id = 'source_dataset_{}'.format(_millis())
source_dataset_ref = client.dataset(source_dataset_id)
source_dataset = bigquery.Dataset(source_dataset_ref)
source_dataset = client.create_dataset(source_dataset)
to_delete.append(source_dataset)

job_config = bigquery.LoadJobConfig()
job_config.schema = [
bigquery.SchemaField('name', 'STRING'),
bigquery.SchemaField('post_abbr', 'STRING')
]
job_config.skip_leading_rows = 1
uri = 'gs://cloud-samples-data/bigquery/us-states/us-states.csv'
source_table_id = 'us_states'
load_job = client.load_table_from_uri(
uri, source_dataset.table(source_table_id), job_config=job_config)
load_job.result()

shared_dataset_id = 'shared_dataset_{}'.format(_millis())
shared_dataset_ref = client.dataset(shared_dataset_id)
shared_dataset = bigquery.Dataset(shared_dataset_ref)
shared_dataset = client.create_dataset(shared_dataset)
to_delete.append(shared_dataset)

# [START bigquery_create_view]
# from google.cloud import bigquery
# client = bigquery.Client()
# project = 'my-project'
# source_dataset_ref = client.dataset('my_source_dataset')
# shared_dataset_ref = client.dataset('my_shared_dataset')

# This example shows how to create a shared view of a source table of
# US States. The source table contains all 50 states, while the view will
# contain only states with names starting with 'W'.
view_ref = shared_dataset_ref.table('my_shared_view')
view = bigquery.Table(view_ref)
sql_template = (
'SELECT name, post_abbr FROM `{}.{}.{}` WHERE name LIKE "W%"')
view.view_query = sql_template.format(
project, source_dataset_id, source_table_id)
view = client.create_table(view) # API request

print('Successfully created view at {}'.format(view.full_table_id))
# [END bigquery_create_view]

# [START bigquery_update_view_query]
# from google.cloud import bigquery
# client = bigquery.Client()
# project = 'my-project'
# source_dataset_ref = client.dataset('my_source_dataset')
# shared_dataset_ref = client.dataset('my_shared_dataset')

# This example shows how to update a shared view of a source table of
# US States. The view's query will be updated to contain only states with
# names starting with 'M'.
view_ref = shared_dataset_ref.table('my_shared_view')
view = bigquery.Table(view_ref)
sql_template = (
'SELECT name, post_abbr FROM `{}.{}.{}` WHERE name LIKE "M%"')
view.view_query = sql_template.format(
project, source_dataset_id, source_table_id)
view = client.update_table(view, ['view_query']) # API request
# [END bigquery_update_view_query]

# [START bigquery_get_view]
# from google.cloud import bigquery
# client = bigquery.Client()
# shared_dataset_id = 'my_shared_dataset'

view_ref = client.dataset(shared_dataset_id).table('my_shared_view')
view = client.get_table(view_ref) # API Request

# Display view properties
print('View at {}'.format(view.full_table_id))
print('View Query:\n{}'.format(view.view_query))
# [END bigquery_get_view]
assert view.view_query is not None

analyst_group_email = 'example-analyst-group@google.com'
# [START bigquery_grant_view_access]
# from google.cloud import bigquery
# client = bigquery.Client()

# Assign access controls to the dataset containing the view
# shared_dataset_id = 'my_shared_dataset'
# analyst_group_email = 'data_analysts@example.com'
shared_dataset = client.get_dataset(
client.dataset(shared_dataset_id)) # API request
access_entries = shared_dataset.access_entries
access_entries.append(
bigquery.AccessEntry('READER', 'groupByEmail', analyst_group_email)
)
shared_dataset.access_entries = access_entries
shared_dataset = client.update_dataset(
shared_dataset, ['access_entries']) # API request

# Authorize the view to access the source dataset
# project = 'my-project'
# source_dataset_id = 'my_source_dataset'
source_dataset = client.get_dataset(
client.dataset(source_dataset_id)) # API request
view_reference = {
'projectId': project,
'datasetId': shared_dataset_id,
'tableId': 'my_shared_view',
}
access_entries = source_dataset.access_entries
access_entries.append(
bigquery.AccessEntry(None, 'view', view_reference)
)
source_dataset.access_entries = access_entries
source_dataset = client.update_dataset(
source_dataset, ['access_entries']) # API request
# [END bigquery_grant_view_access]


def test_table_insert_rows(client, to_delete):
"""Insert / fetch table data."""
dataset_id = 'table_insert_rows_dataset_{}'.format(_millis())
Expand Down Expand Up @@ -2648,7 +2766,7 @@ def test_query_external_gcs_temporary_table(client):
job_config.table_definitions = {table_id: external_config}

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

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

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

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

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

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

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

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

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

query_job = client.query(sql) # API request
Expand Down