Skip to main content
Join the official Python Developers Survey 2018 and win valuable prizes: Start the survey!

BigQuery-DatasetManager is a simple file-based CLI management tool for BigQuery Datasets.

Project description

https://img.shields.io/pypi/pyversions/BigQuery-DatasetManager.svg https://travis-ci.org/laughingman7743/BigQuery-DatasetManager.svg?branch=master https://codecov.io/gh/laughingman7743/BigQuery-DatasetManager/branch/master/graph/badge.svg https://img.shields.io/pypi/l/BigQuery-DatasetManager.svg

BigQuery-DatasetManager

BigQuery-DatasetManager is a simple file-based CLI management tool for BigQuery Datasets.

Requirements

  • Python
    • CPython 2,7, 3,4, 3.5, 3.6

Installation

$ pip install BigQuery-DatasetManager

Resource representation

The resource representation of the dataset and the table is described in YAML format.

Dataset

name: dataset1
friendly_name: null
description: null
default_table_expiration_ms: null
location: US
access_entries:
-   role: OWNER
    entity_type: specialGroup
    entity_id: projectOwners
-   role: WRITER
    entity_type: specialGroup
    entity_id: projectWriters
-   role: READER
    entity_type: specialGroup
    entity_id: projectReaders
-   role: OWNER
    entity_type: userByEmail
    entity_id: aaa@bbb.gserviceaccount.com
-   role: null
    entity_type: view
    entity_id:
        datasetId: view1
        projectId: project1
        tableId: table1
labels:
    foo: bar
Key name Value Description
dataset_id str ID of the dataset.
friendly_name str Title of the dataset.
description str Description of the dataset.
default_table_expiration_ms int Default expiration time for tables in the dataset.
location str Location in which the dataset is hosted.
access_entries seq Represents grant of an access role to an entity.
access_entries role str

Role granted to the entity. The following string values are supported:

  • OWNER
  • WRITER
  • READER

It may also be null if the entity_type is view.

entity_type str

Type of entity being granted the role. One of

  • userByEmail
  • groupByEmail
  • domain
  • specialGroup
  • view
entity_id   str/map If the entity_type is not ‘view’, the entity_id is the str ID of the entity being granted the role. If the entity_type is ‘view’, the entity_id is a dict representing the view from a different dataset to grant access to.
datasetId str ID of the dataset containing this table. (Specifies when entity_type is view.)
projectId str ID of the project containing this table. (Specifies when entity_type is view.)
tableId str ID of the table. (Specifies when entity_type is view.)
labels map Labels for the dataset.

NOTE: See the official documentation of BigQuery Datasets for details of key names.

Table

table_id: table1
friendly_name: null
description: null
expires: null
partitioning_type: null
view_use_legacy_sql: null
view_query: null
schema:
-   name: column1
    field_type: STRING
    mode: REQUIRED
    description: null
    fields: null
-   name: column2
    field_type: RECORD
    mode: NULLABLE
    description: null
    fields:
    -   name: column2_1
        field_type: STRING
        mode: NULLABLE
        description: null
        fields: null
    -   name: column2_2
        field_type: INTEGER
        mode: NULLABLE
        description: null
        fields: null
    -   name: column2_3
        field_type: RECORD
        mode: REPEATED
        description: null
        fields:
        -   name: column2_3_1
            field_type: BOOLEAN
            mode: NULLABLE
            description: null
            fields: null
labels:
    foo: bar
table_id: view1
friendly_name: null
description: null
expires: null
partitioning_type: null
view_use_legacy_sql: false
view_query: |
    select
    *
    from
    `project1.dataset1.table1`
schema: null
labels: null
Key name Value Description
table_id str ID of the table.
friendly_name str Title of the table.
description str Description of the table.
expires str Datetime at which the table will be deleted. (ISO8601 format %Y-%m-%dT%H:%M:%S.%f%z)
partitioning_type str Time partitioning of the table if it is partitioned. The only partitioning type that is currently supported is DAY.
view_use_legacy_sql bool Specifies whether to use BigQuery’s legacy SQL for this view.
view_query str SQL query defining the table as a view.
schema seq The schema of the table destination for the row.
schema name str The name of the field.
field_type str

The type of the field. One of

  • STRING
  • BYTES
  • INTEGER
  • INT64 (same as INTEGER)
  • FLOAT
  • FLOAT64 (same as FLOAT)
  • BOOLEAN
  • BOOL (same as BOOLEAN)
  • TIMESTAMP
  • DATE
  • TIME
  • DATETIME
  • RECORD (where RECORD indicates that the field contains a nested schema)
  • STRUCT (same as RECORD)
mode str

The mode of the field. One of

  • NULLABLE
  • REQUIRED
  • REPEATED
description str Description for the field.
fields seq Describes the nested schema fields if the type property is set to RECORD.
labels map Labels for the table.

NOTE: See the official documentation of BigQuery Tables for details of key names.

Directory structure

.
├── dataset1        # Directory storing the table configuration file of dataset1.
│   ├── table1.yml  # Configuration file of table1 in dataset1.
│   └── table2.yml  # Configuration file of table2 in dataset1.
├── dataset1.yml    # Configuration file of dataset1.
├── dataset2        # Directory storing the table configuration file of dataset2.
│   └── .gitkeep    # When keeping a directory, dataset2 is empty.
├── dataset2.yml    # Configuration file of dataset2.
└── dataset3.yml    # Configuration file of dataset3. This dataset does not manage the table.

NOTE: If you do not want to manage the table, delete the directory with the same name as the dataset name.

Usage

Usage: bqdm [OPTIONS] COMMAND [ARGS]...

Options:
  -c, --credential-file PATH  Location of credential file for service accounts.
  -p, --project TEXT          Project ID for the project which you’d like to manage with.
  --color / --no-color        Enables output with coloring.
  --parallelism INTEGER       Limit the number of concurrent operation.
  --debug                     Debug output management.
  -h, --help                  Show this message and exit.

Commands:
  apply    Builds or changes datasets.
  destroy  Specify subcommand `plan` or `apply`
  export   Export existing datasets into file in YAML format.
  plan     Generate and show an execution plan.

Export

Usage: bqdm export [OPTIONS] [OUTPUT_DIR]

  Export existing datasets into file in YAML format.

Options:
  -d, --dataset TEXT          Specify the ID of the dataset to manage.
  -e, --exclude-dataset TEXT  Specify the ID of the dataset to exclude from managed.
  -h, --help                  Show this message and exit.

Plan

Usage: bqdm plan [OPTIONS] [CONF_DIR]

  Generate and show an execution plan.

Options:
  --detailed_exitcode         Return a detailed exit code when the command exits.
                              When provided, this argument changes
                              the exit codes and their meanings to provide
                              more granular information about what the
                              resulting plan contains:
                              0 = Succeeded with empty diff
                              1 = Error
                              2 = Succeeded with non-
                              empty diff
  -d, --dataset TEXT          Specify the ID of the dataset to manage.
  -e, --exclude-dataset TEXT  Specify the ID of the dataset to exclude from managed.
  -h, --help                  Show this message and exit.

Apply

Usage: bqdm apply [OPTIONS] [CONF_DIR]

  Builds or changes datasets.

Options:
  -d, --dataset TEXT              Specify the ID of the dataset to manage.
  -e, --exclude-dataset TEXT      Specify the ID of the dataset to exclude from managed.
  -m, --mode [select_insert|select_insert_backup|replace|replace_backup|drop_create|drop_create_backup]
                                  Specify the migration mode when changing the schema.
                                  Choice from `select_insert`,
                                  `select_insert_backup`, `replace`, r`eplace_backup`,
                                  `drop_create`,
                                  `drop_create_backup`.  [required]
  -b, --backup-dataset TEXT       Specify the ID of the dataset to store the backup at migration
  -h, --help                      Show this message and exit.

NOTE: See migration mode

Destroy

Usage: bqdm destroy [OPTIONS] COMMAND [ARGS]...

  Specify subcommand `plan` or `apply`

Options:
  -h, --help  Show this message and exit.

Commands:
  apply  Destroy managed datasets.
  plan   Generate and show an execution plan for...
Destroy plan
Usage: bqdm destroy plan [OPTIONS] [CONF_DIR]

  Generate and show an execution plan for datasets destruction.

Options:
  --detailed-exitcode         Return a detailed exit code when the command exits.
                              When provided, this argument changes
                              the exit codes and their meanings to provide
                              more granular information about what the
                              resulting plan contains:
                              0 = Succeeded with empty diff
                              1 = Error
                              2 = Succeeded with non-
                              empty diff
  -d, --dataset TEXT          Specify the ID of the dataset to manage.
  -e, --exclude-dataset TEXT  Specify the ID of the dataset to exclude from managed.
  -h, --help                  Show this message and exit.
Destroy apply
Usage: bqdm destroy apply [OPTIONS] [CONF_DIR]

  Destroy managed datasets.

Options:
  -d, --dataset TEXT          Specify the ID of the dataset to manage.
  -e, --exclude-dataset TEXT  Specify the ID of the dataset to exclude from managed.
  -h, --help                  Show this message and exit.

Migration mode

select_insert

  1. TODO

LIMITATIONS: TODO

select_insert_backup

  1. TODO

LIMITATIONS: TODO

replace

  1. TODO

LIMITATIONS: TODO

replace_backup

  1. TODO

LIMITATIONS: TODO

drop_create

  1. TODO

drop_create_backup

  1. TODO

Authentication

See authentication section in the official documentation of google-cloud-python.

If you’re running in Compute Engine or App Engine, authentication should “just work”.

If you’re developing locally, the easiest way to authenticate is using the Google Cloud SDK:

$ gcloud auth application-default login

Note that this command generates credentials for client libraries. To authenticate the CLI itself, use:

$ gcloud auth login

Previously, gcloud auth login was used for both use cases. If your gcloud installation does not support the new command, please update it:

$ gcloud components update

If you’re running your application elsewhere, you should download a service account JSON keyfile and point to it using an environment variable:

$ export GOOGLE_APPLICATION_CREDENTIALS="/path/to/keyfile.json"

Testing

Depends on the following environment variables:

$ export GOOGLE_APPLICATION_CREDENTIALS=/path/to/credentials.json
$ export GOOGLE_CLOUD_PROJECT=YOUR_PROJECT_ID

Run test

$ pip install pipenv
$ pipenv install --dev
$ pipenv run pytest

Run test multiple Python versions

$ pip install pipenv
$ pipenv install --dev
$ pyenv local 3.6.5 3.5.5 3.4.8 2.7.14
$ pipenv run tox

TODO

  1. Support encryption configuration for table
  2. Support external data configuration for table
  3. Schema replication
  4. Integration tests

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Filename, size & hash SHA256 hash help File type Python version Upload date
BigQuery_DatasetManager-0.1.6-py2.py3-none-any.whl (32.0 kB) Copy SHA256 hash SHA256 Wheel py2.py3 Jul 2, 2018
BigQuery-DatasetManager-0.1.6.tar.gz (30.9 kB) Copy SHA256 hash SHA256 Source None Jul 2, 2018

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN SignalFx SignalFx Supporter DigiCert DigiCert EV certificate StatusPage StatusPage Status page