<jupyter_start><jupyter_text>BigQuery Magic Commands and DMLThe examples in this notebook introduce features of [BigQuery Standard SQL](https://cloud.google.com/bigquery/sql-reference/) and [BigQuery SQL Data Manipulation Language (beta)](https://cloud.google.com/bigquery/sql-reference/dml-syntax). BigQuery Standard SQL is compliant with the SQL 2011 standard. You've already seen the use of the magic command `%%bq` in the [Hello BigQuery](Hello BigQuery.ipynb) and [BigQuery Commands](BigQuery Commands.ipynb) notebooks. This command and others in the Google Cloud Datalab API support BigQuery Standard SQL.  Using the BigQuery Magic command with Standard SQLFirst, we will cover some more uses of the `%%bq` magic command. Let's define a query to work with:<jupyter_code>%%bq query --name UniqueNames2013
WITH UniqueNames2013 AS
(SELECT DISTINCT name
  FROM `bigquery-public-data.usa_names.usa_1910_2013`
  WHERE Year = 2013)
SELECT * FROM UniqueNames2013<jupyter_output><empty_output><jupyter_text>Now let's list all available commands to work with `%%bq`<jupyter_code>%%bq -h<jupyter_output>usage: bq [-h]
          {datasets,tables,query,execute,extract,sample,dryrun,udf,datasource,load}
          ...

Execute various BigQuery-related operations. Use "%bq <command> -h" for help
on a specific command.

positional arguments:
  {datasets,tables,query,execute,extract,sample,dryrun,udf,datasource,load}
                        commands
    datasets            Operations on BigQuery datasets
    tables              Operations on BigQuery tables
    query               Create or execute a BigQuery SQL query object,
                        optionally using other SQL objects, UDFs, or external
                        datasources. If a query name is not specified, the
                        query is executed.
    execute             Execute a BigQuery SQL query and optionally send the
                        results to a named table. The cell can optionally
                        contain arguments for expanding variables in the
                        query.
    extract           [...]<jupyter_text>The `dryrun` argument in ``%%bq`` can be helpful to confirm the syntax of the SQL query. Instead of executing the query, it will only return some statistics:<jupyter_code>%%bq dryrun -q UniqueNames2013<jupyter_output><empty_output><jupyter_text>Now, let's get a small sample of the results using the `sample` argument in ``%%bq``:<jupyter_code>%%bq sample -q UniqueNames2013<jupyter_output><empty_output><jupyter_text>Finally, We can use the `execute` command in %%bq to display the results of our query:<jupyter_code>%%bq execute -q UniqueNames2013<jupyter_output><empty_output><jupyter_text>Using Google BigQuery SQL Data Manipulation LanguageBelow, we will demonstrate how to use Google BigQuery SQL Data Manipulation Language (DML) in Datalab.  PreparationFirst, let's import the BigQuery module, and create a sample dataset and table to help demonstrate the features of Google BigQuery DML.<jupyter_code>import google.datalab.bigquery as bq
# Create a new dataset (this will be deleted later in the notebook)
sample_dataset = bq.Dataset('sampleDML')
if not sample_dataset.exists():
  sample_dataset.create(friendly_name = 'Sample Dataset for testing DML', description = 'Created from Sample Notebook in Google Cloud Datalab')
  sample_dataset.exists()
# To create a table, we need to create a schema for it.
# Its easiest to create a schema from some existing data, so this
# example demonstrates using an example object
fruit_row = {
  'name': 'string value',
  'count': 0
}

sample_table1 = bq.Table("sampleDML.fruit_basket").create(schema = bq.Schema.from_data([fruit_row]), 
                                                          overwrite = True)<jupyter_output><empty_output><jupyter_text>Inserting DataWe can add rows to our newly created `fruit_basket` table by using an `INSERT` statement in our BigQuery Standard SQL query.<jupyter_code>%%bq query
INSERT sampleDML.fruit_basket (name, count)
VALUES('banana', 5),
      ('orange', 10),
      ('apple', 15),
      ('mango', 20)<jupyter_output><empty_output><jupyter_text>You may rewrite the previous query as:<jupyter_code>%%bq query
INSERT sampleDML.fruit_basket (name, count)
SELECT * 
FROM UNNEST([('peach', 25), ('watermelon', 30)])<jupyter_output><empty_output><jupyter_text>You can also use a `WITH` clause with `INSERT` and `SELECT`.<jupyter_code>%%bq query
INSERT sampleDML.fruit_basket(name, count)
WITH w AS (
  SELECT ARRAY<STRUCT<name string, count int64>>
      [('cherry', 35),
      ('cranberry', 40),
      ('pear', 45)] col
)
SELECT name, count FROM w, UNNEST(w.col)<jupyter_output><empty_output><jupyter_text>Here is an example that copies one table's contents into another. First we will create a new table.<jupyter_code>fruit_row_detailed = {
  'name': 'string value',
  'count': 0,
  'readytoeat': False
}
sample_table2 = bq.Table("sampleDML.fruit_basket_detailed").create(schema = bq.Schema.from_data([fruit_row_detailed]), 
                                                                   overwrite = True)
%%bq query
INSERT sampleDML.fruit_basket_detailed (name, count, readytoeat)
SELECT name, count, false
FROM sampleDML.fruit_basket<jupyter_output><empty_output><jupyter_text>Updating DataYou can update rows in the `fruit_basket` table by using an `UPDATE` statement in the BigQuery Standard SQL query. We will try to do this using the Datalab BigQuery API.<jupyter_code>%%bq query
UPDATE sampleDML.fruit_basket_detailed
SET readytoeat = True
WHERE name = 'banana'<jupyter_output><empty_output><jupyter_text>To view the contents of a table in BigQuery, use `%%bq tables view` command:<jupyter_code>%%bq tables view --name sampleDML.fruit_basket_detailed<jupyter_output><empty_output><jupyter_text>Deleting DataYou can delete rows in the `fruit_basket` table by using a `DELETE` statement in the BigQuery Standard SQL query.<jupyter_code>%%bq query
DELETE sampleDML.fruit_basket
WHERE name in ('cherry', 'cranberry')<jupyter_output><empty_output><jupyter_text>Use the following query to delete the corresponding entries in `sampleDML.fruit_basket_detailed`<jupyter_code>%%bq query
DELETE sampleDML.fruit_basket_detailed
WHERE NOT EXISTS
  (SELECT * FROM sampleDML.fruit_basket
  WHERE fruit_basket_detailed.name = fruit_basket.name)<jupyter_output><empty_output><jupyter_text>Deleting Resources<jupyter_code># Clear out sample resources
sample_dataset.delete(delete_contents = True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08675659104843654, 'fraction_numerical': 0.009656652360515022, 'mean_word_length': 4.357142857142857, 'pattern_counts': {'":': 0, '<': 63, '<?xml version=': 0, '>': 63, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '327459', 'license': 'MIT', 'n_tokens_mistral': 2043, 'n_tokens_neox': 1921, 'n_words': 632, 'path': 'cloud/datalab/notebooks_ejemplo/BigQuery+Magic+Commands+and+DML.ipynb', 'repo_name': 'zentonllo/tfg-tensorflow', 'size': '6524'}