def _execute_cell(args, cell_body):
"""Implements the BigQuery cell magic used to execute BQ queries.
The supported syntax is:
%%bq execute <args>
[<inline SQL>]
Args:
args: the optional arguments following '%%bq execute'.
cell_body: optional contents of the cell
Returns:
QueryResultsTable containing query result
"""
query = google.datalab.utils.commands.get_notebook_item(args['query'])
if args['verbose']:
print(query.sql)
query_params = _get_query_parameters(args, cell_body)
if args['to_dataframe']:
# re-parse the int arguments because they're passed as strings
start_row = int(args['dataframe_start_row']) if args['dataframe_start_row'] else None
max_rows = int(args['dataframe_max_rows']) if args['dataframe_max_rows'] else None
output_options = QueryOutput.dataframe(start_row=start_row, max_rows=max_rows,
use_cache=not args['nocache'])
else:
output_options = QueryOutput.table(name=args['table'], mode=args['mode'],
use_cache=not args['nocache'],
allow_large_results=args['large'])
context = _construct_context_for_args(args)
r = query.execute(output_options, context=context, query_params=query_params)
return r.result()
def _extract_cell(args, cell_body):
"""Implements the BigQuery extract magic used to extract query or table data to GCS.
The supported syntax is:
%bq extract <args>
Args:
args: the arguments following '%bigquery extract'.
"""
if args['table']:
source = _get_table(args['table'])
if not source:
raise Exception('Could not find table %s' % args['table'])
job = source.extract(args['path'],
format='CSV' if args['format'] == 'csv' else
'NEWLINE_DELIMITED_JSON',
csv_delimiter=args['delimiter'],
csv_header=args['header'],
compress=args['compress'])
elif args['query'] or args['view']:
source_name = args['view'] or args['query']
source = google.datalab.utils.commands.get_notebook_item(source_name)
if not source:
raise Exception('Could not find ' +
('view ' +
args['view'] if args['view'] else 'query ' +
args['query']))
query = source if args[
'query'] else google.datalab.bigquery.Query.from_view(source)
query_params = _get_query_parameters(
args, cell_body) if args['query'] else None
output_options = QueryOutput.file(path=args['path'],
format=args['format'],
csv_delimiter=args['delimiter'],
csv_header=args['header'],
compress=args['compress'],
use_cache=not args['nocache'])
context = _construct_context_for_args(args)
job = query.execute(output_options,
context=context,
query_params=query_params)
else:
raise Exception('A query, table, or view is needed to extract')
if job.failed:
raise Exception('Extract failed: %s' % str(job.fatal_error))
elif job.errors:
raise Exception('Extract completed with errors: %s' % str(job.errors))
return job.result()
def _execute_cell(args, cell_body):
"""Implements the BigQuery cell magic used to execute BQ queries.
The supported syntax is:
%%bq execute <args>
[<inline SQL>]
Args:
args: the optional arguments following '%%bq execute'.
cell_body: optional contents of the cell
Returns:
QueryResultsTable containing query result
"""
env = google.datalab.utils.commands.notebook_environment()
config = google.datalab.utils.commands.parse_config(cell_body, env, False) or {}
parameters = config.get('parameters') or []
if parameters:
jsonschema.validate({'parameters': parameters}, BigQuerySchema.QUERY_PARAMS_SCHEMA)
table_name = google.datalab.bigquery.Query.resolve_parameters(args['table'], parameters)
query = google.datalab.utils.commands.get_notebook_item(args['query'])
if args['verbose']:
print(query.sql)
query_params = get_query_parameters(args, cell_body)
if args['to_dataframe']:
# re-parse the int arguments because they're passed as strings
start_row = int(args['dataframe_start_row']) if args['dataframe_start_row'] else None
max_rows = int(args['dataframe_max_rows']) if args['dataframe_max_rows'] else None
output_options = QueryOutput.dataframe(start_row=start_row, max_rows=max_rows,
use_cache=not args['nocache'])
else:
output_options = QueryOutput.table(
name=table_name, mode=args['mode'], use_cache=not args['nocache'],
allow_large_results=args['large'])
context = google.datalab.utils._utils._construct_context_for_args(args)
r = query.execute(output_options, context=context, query_params=query_params)
return r.result()
def _execute_cell(args, cell_body):
"""Implements the BigQuery cell magic used to execute BQ queries.
The supported syntax is:
%%bq execute <args>
[<inline SQL>]
Args:
args: the optional arguments following '%%bq execute'.
cell_body: optional contents of the cell
Returns:
QueryResultsTable containing query result
"""
env = google.datalab.utils.commands.notebook_environment()
config = google.datalab.utils.commands.parse_config(cell_body, env, False) or {}
parameters = config.get('parameters') or []
if parameters:
jsonschema.validate({'parameters': parameters}, BigQuerySchema.QUERY_PARAMS_SCHEMA)
table_name = google.datalab.bigquery.Query.resolve_parameters(args['table'], parameters)
query = google.datalab.utils.commands.get_notebook_item(args['query'])
if args['verbose']:
print(query.sql)
query_params = get_query_parameters(args, cell_body)
if args['to_dataframe']:
# re-parse the int arguments because they're passed as strings
start_row = int(args['dataframe_start_row']) if args['dataframe_start_row'] else None
max_rows = int(args['dataframe_max_rows']) if args['dataframe_max_rows'] else None
output_options = QueryOutput.dataframe(start_row=start_row, max_rows=max_rows,
use_cache=not args['nocache'])
else:
output_options = QueryOutput.table(
name=table_name, mode=args['mode'], use_cache=not args['nocache'],
allow_large_results=args['large'])
context = google.datalab.utils._utils._construct_context_for_args(args)
r = query.execute(output_options, context=context, query_params=query_params)
return r.result()
def _execute_cell(args, cell_body):
"""Implements the BigQuery cell magic used to execute BQ queries.
The supported syntax is:
%%bq execute <args>
[<inline SQL>]
Args:
args: the optional arguments following '%%bq execute'.
cell_body: optional contents of the cell
Returns:
QueryResultsTable containing query result
"""
query = google.datalab.utils.commands.get_notebook_item(args['query'])
if args['verbose']:
print(query.sql)
query_params = _get_query_parameters(args, cell_body)
if args['to_dataframe']:
# re-parse the int arguments because they're passed as strings
start_row = int(args['dataframe_start_row']
) if args['dataframe_start_row'] else None
max_rows = int(
args['dataframe_max_rows']) if args['dataframe_max_rows'] else None
output_options = QueryOutput.dataframe(start_row=start_row,
max_rows=max_rows,
use_cache=not args['nocache'])
else:
output_options = QueryOutput.table(name=args['table'],
mode=args['mode'],
use_cache=not args['nocache'],
allow_large_results=args['large'])
context = _construct_context_for_args(args)
r = query.execute(output_options,
context=context,
query_params=query_params)
return r.result()
def _extract_cell(args, cell_body):
"""Implements the BigQuery extract magic used to extract query or table data to GCS.
The supported syntax is:
%bq extract <args>
Args:
args: the arguments following '%bigquery extract'.
"""
env = google.datalab.utils.commands.notebook_environment()
config = google.datalab.utils.commands.parse_config(cell_body, env, False) or {}
parameters = config.get('parameters')
if args['table']:
table = google.datalab.bigquery.Query.resolve_parameters(args['table'], parameters)
source = _get_table(table)
if not source:
raise Exception('Could not find table %s' % table)
csv_delimiter = args['delimiter'] if args['format'] == 'csv' else None
path = google.datalab.bigquery.Query.resolve_parameters(args['path'], parameters)
job = source.extract(path, format=args['format'], csv_delimiter=csv_delimiter,
csv_header=args['header'], compress=args['compress'])
elif args['query'] or args['view']:
source_name = args['view'] or args['query']
source = google.datalab.utils.commands.get_notebook_item(source_name)
if not source:
raise Exception('Could not find ' +
('view ' + args['view'] if args['view'] else 'query ' + args['query']))
query = source if args['query'] else bigquery.Query.from_view(source)
query_params = get_query_parameters(args, cell_body) if args['query'] else None
output_options = QueryOutput.file(path=args['path'], format=args['format'],
csv_delimiter=args['delimiter'],
csv_header=args['header'], compress=args['compress'],
use_cache=not args['nocache'])
context = google.datalab.utils._utils._construct_context_for_args(args)
job = query.execute(output_options, context=context, query_params=query_params)
else:
raise Exception('A query, table, or view is needed to extract')
if job.failed:
raise Exception('Extract failed: %s' % str(job.fatal_error))
elif job.errors:
raise Exception('Extract completed with errors: %s' % str(job.errors))
return job.result()
def _extract_cell(args, cell_body):
"""Implements the BigQuery extract magic used to extract query or table data to GCS.
The supported syntax is:
%bq extract <args>
Args:
args: the arguments following '%bigquery extract'.
"""
if args['table']:
source = _get_table(args['table'])
if not source:
raise Exception('Could not find table %s' % args['table'])
job = source.extract(args['path'],
format='CSV' if args['format'] == 'csv' else 'NEWLINE_DELIMITED_JSON',
csv_delimiter=args['delimiter'], csv_header=args['header'],
compress=args['compress'])
elif args['query'] or args['view']:
source = google.datalab.utils.commands.get_notebook_item(args['view'])
if not source:
raise Exception('Could not find ' +
('view ' + args['view'] if args['view'] else 'query ' + args['query']))
query = source if args['query'] else google.datalab.bigquery.Query.from_view(source)
query_params = _get_query_parameters(args, cell_body) if args['query'] else None
output_options = QueryOutput.file(path=args['path'], format=args['format'],
csv_delimiter=args['delimiter'],
csv_header=args['header'], compress=args['compress'],
use_cache=not args['nocache'])
context = _construct_context_for_args(args)
job = query.execute(output_options, context=context, query_params=query_params)
else:
raise Exception('A query, table, or view is needed to extract')
if job.failed:
raise Exception('Extract failed: %s' % str(job.fatal_error))
elif job.errors:
raise Exception('Extract completed with errors: %s' % str(job.errors))
return job.result()
def _sample_cell(args, cell_body):
"""Implements the BigQuery sample magic for sampling queries
The supported sytanx is:
%%bq sample <args>
[<inline SQL>]
Args:
args: the optional arguments following '%%bq sample'.
cell_body: optional contents of the cell
Returns:
The results of executing the sampling query, or a profile of the sample data.
"""
query = None
table = None
view = None
query_params = None
if args['query']:
query = google.datalab.utils.commands.get_notebook_item(args['query'])
if query is None:
raise Exception('Cannot find query %s.' % args['query'])
query_params = _get_query_parameters(args, cell_body)
elif args['table']:
table = _get_table(args['table'])
if not table:
raise Exception('Could not find table %s' % args['table'])
elif args['view']:
view = google.datalab.utils.commands.get_notebook_item(args['view'])
if not isinstance(view, google.datalab.bigquery.View):
raise Exception('Could not find view %s' % args['view'])
else:
raise Exception('A query, table, or view is neede to sample')
# parse comma-separated list of fields
fields = args['fields'].split(',') if args['fields'] else None
count = int(args['count']) if args['count'] else None
percent = int(args['percent']) if args['percent'] else None
sampling = Sampling._auto(method=args['method'],
fields=fields,
count=count,
percent=percent,
key_field=args['key_field'],
ascending=(args['order'] == 'ascending'))
context = _construct_context_for_args(args)
if view:
query = google.datalab.bigquery.Query.from_view(view)
elif table:
query = google.datalab.bigquery.Query.from_table(table)
if args['profile']:
results = query.execute(QueryOutput.dataframe(),
sampling=sampling,
context=context,
query_params=query_params).result()
else:
results = query.execute(QueryOutput.table(),
sampling=sampling,
context=context,
query_params=query_params).result()
if args['verbose']:
print(query.sql)
if args['profile']:
return google.datalab.utils.commands.profile_df(results)
else:
return results
def _sample_cell(args, cell_body):
"""Implements the BigQuery sample magic for sampling queries
The supported sytanx is:
%%bq sample <args>
[<inline SQL>]
Args:
args: the optional arguments following '%%bq sample'.
cell_body: optional contents of the cell
Returns:
The results of executing the sampling query, or a profile of the sample data.
"""
query = None
table = None
view = None
query_params = None
if args['query']:
query = google.datalab.utils.commands.get_notebook_item(args['query'])
if query is None:
raise Exception('Cannot find query %s.' % args['query'])
query_params = _get_query_parameters(args, cell_body)
elif args['table']:
table = _get_table(args['table'])
if not table:
raise Exception('Could not find table %s' % args['table'])
elif args['view']:
view = google.datalab.utils.commands.get_notebook_item(args['view'])
if not isinstance(view, google.datalab.bigquery.View):
raise Exception('Could not find view %s' % args['view'])
else:
raise Exception('A query, table, or view is neede to sample')
# parse comma-separated list of fields
fields = args['fields'].split(',') if args['fields'] else None
count = int(args['count']) if args['count'] else None
percent = int(args['percent']) if args['percent'] else None
sampling = Sampling._auto(method=args['method'], fields=fields, count=count, percent=percent,
key_field=args['key_field'], ascending=(args['order'] == 'ascending'))
context = _construct_context_for_args(args)
if view:
query = google.datalab.bigquery.Query.from_view(view)
elif table:
query = google.datalab.bigquery.Query.from_table(table)
if args['profile']:
results = query.execute(QueryOutput.dataframe(), sampling=sampling,
context=context, query_params=query_params).result()
else:
results = query.execute(QueryOutput.table(), sampling=sampling, context=context,
query_params=query_params).result()
if args['verbose']:
print(query.sql)
if args['profile']:
return google.datalab.utils.commands.profile_df(results)
else:
return results