<jupyter_start><jupyter_text>DV360 SDF To BigQueryDownload SDF reports into a BigQuery table. LicenseCopyright 2020 Google LLC,Licensed under the Apache License, Version 2.0 (the "License");you may not use this file except in compliance with the License.You may obtain a copy of the License at  https://www.apache.org/licenses/LICENSE-2.0Unless required by applicable law or agreed to in writing, softwaredistributed under the License is distributed on an "AS IS" BASIS,WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.See the License for the specific language governing permissions andlimitations under the License. DisclaimerThis is not an officially supported Google product. It is a reference implementation. There is absolutely NO WARRANTY provided for using this code. The code is Apache Licensed and CAN BE fully modified, white labeled, and disassembled by your team.This code generated (see starthinker/scripts for possible source):  - **Command**: "python starthinker_ui/manage.py colab"  - **Command**: "python starthinker/tools/colab.py [JSON RECIPE]" 1. Install DependenciesFirst install the libraries needed to execute recipes, this only needs to be done once, then click play.<jupyter_code>!pip install git+https://github.com/google/starthinker<jupyter_output><empty_output><jupyter_text>2. Set ConfigurationThis code is required to initialize the project. Fill in required fields and press play.1. If the recipe uses a Google Cloud Project:  - Set the configuration **project** value to the project identifier from [these instructions](https://github.com/google/starthinker/blob/master/tutorials/cloud_project.md).1. If the recipe has **auth** set to **user**:  - If you have user credentials:    - Set the configuration **user** value to your user credentials JSON.  - If you DO NOT have user credentials:    - Set the configuration **client** value to [downloaded client credentials](https://github.com/google/starthinker/blob/master/tutorials/cloud_client_installed.md).1. If the recipe has **auth** set to **service**:  - Set the configuration **service** value to [downloaded service credentials](https://github.com/google/starthinker/blob/master/tutorials/cloud_service.md).<jupyter_code>from starthinker.util.configuration import Configuration


CONFIG = Configuration(
  project="",
  client={},
  service={},
  user="/content/user.json",
  verbose=True
)<jupyter_output><empty_output><jupyter_text>3. Enter DV360 SDF To BigQuery Recipe Parameters 1. Select your filter types and the filter ideas. 1. Enter the file types using commas. 1. SDF_ will be prefixed to all tables and date appended to daily tables. 1. File types take the following format: FILE_TYPE_CAMPAIGN, FILE_TYPE_AD_GROUP,...Modify the values below for your use case, can be done multiple times, then click play.<jupyter_code>FIELDS = {
  'auth_write': 'service',  # Credentials used for writing data.
  'partner_id': '',  # The sdf file types.
  'file_types': [],  # The sdf file types.
  'filter_type': '',  # The filter type for the filter ids.
  'filter_ids': [],  # Comma separated list of filter ids for the request.
  'dataset': '',  # Dataset to be written to in BigQuery.
  'version': '5',  # The sdf version to be returned.
  'table_suffix': '',  # Optional: Suffix string to put at the end of the table name (Must contain alphanumeric or underscores)
  'time_partitioned_table': False,  # Is the end table a time partitioned
  'create_single_day_table': False,  # Would you like a separate table for each day? This will result in an extra table each day and the end table with the most up to date SDF.
}

print("Parameters Set To: %s" % FIELDS)<jupyter_output><empty_output><jupyter_text>4. Execute DV360 SDF To BigQueryThis does NOT need to be modified unless you are changing the recipe, click play.<jupyter_code>from starthinker.util.configuration import execute
from starthinker.util.recipe import json_set_fields

TASKS = [
  {
    'dataset': {
      'auth': 'user', 
      'dataset': {'field': {'name': 'dataset', 'kind': 'string', 'order': 6, 'default': '', 'description': 'Dataset to be written to in BigQuery.'}}
    }
  }, 
  {
    'sdf': {
      'auth': 'user', 
      'version': {'field': {'name': 'version', 'kind': 'choice', 'order': 6, 'default': '5', 'description': 'The sdf version to be returned.', 'choices': ['SDF_VERSION_5', 'SDF_VERSION_5_1']}}, 
      'partner_id': {'field': {'name': 'partner_id', 'kind': 'integer', 'order': 1, 'description': 'The sdf file types.'}}, 
      'file_types': {'field': {'name': 'file_types', 'kind': 'string_list', 'order': 2, 'default': [], 'description': 'The sdf file types.'}}, 
      'filter_type': {'field': {'name': 'filter_type', 'kind': 'choice', 'order': 3, 'default': '', 'description': 'The filter type for the filter ids.', 'choices': ['FILTER_TYPE_ADVERTISER_ID', 'FILTER_TYPE_CAMPAIGN_ID', 'FILTER_TYPE_INSERTION_ORDER_ID', 'FILTER_TYPE_MEDIA_PRODUCT_ID', 'FILTER_TYPE_LINE_ITEM_ID']}}, 
      'read': {
        'filter_ids': {
          'single_cell': True, 
          'values': {'field': {'name': 'filter_ids', 'kind': 'integer_list', 'order': 4, 'default': [], 'description': 'Comma separated list of filter ids for the request.'}}
        }
      }, 
      'time_partitioned_table': {'field': {'name': 'time_partitioned_table', 'kind': 'boolean', 'order': 7, 'default': False, 'description': 'Is the end table a time partitioned'}}, 
      'create_single_day_table': {'field': {'name': 'create_single_day_table', 'kind': 'boolean', 'order': 8, 'default': False, 'description': 'Would you like a separate table for each day? This will result in an extra table each day and the end table with the most up to date SDF.'}}, 
      'dataset': {'field': {'name': 'dataset', 'kind': 'string', 'order': 6, 'default': '', 'description': 'Dataset to be written to in BigQuery.'}}, 
      'table_suffix': {'field': {'name': 'table_suffix', 'kind': 'string', 'order': 6, 'default': '', 'description': 'Optional: Suffix string to put at the end of the table name (Must contain alphanumeric or underscores)'}}
    }
  }
]

json_set_fields(TASKS, FIELDS)

execute(CONFIG, TASKS, force=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12941176470588237, 'fraction_numerical': 0.00692989524576954, 'mean_word_length': 4.821763602251407, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 5, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '608509', 'license': 'Apache-2.0', 'n_tokens_mistral': 1856, 'n_tokens_neox': 1784, 'n_words': 690, 'path': 'colabs/sdf_to_bigquery.ipynb', 'repo_name': 'Ressmann/starthinker', 'size': '6205'}