Skip to content
This repository has been archived by the owner on Dec 29, 2020. It is now read-only.

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

License

Notifications You must be signed in to change notification settings

laughingman7743/BigQuery-DatasetManager

Repository files navigation

https://travis-ci.org/laughingman7743/BigQuery-DatasetManager.svg?branch=master

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

About

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

Topics

Resources

License

Stars

Watchers

Forks

Packages

No packages published

Languages