preset-cli 0.2.2

Running SQL

The CLI offers an easy way to run SQL against an analytical database in a workspace. This can be done programmatically or interactively. For example, to run the query SELECT COUNT(*) AS revenue FROM sales given a workspace URL and a database ID you can run:

% preset-cli --workspaces=https://abcdef12.us1a.app.preset.io/ superset sql \
> --database-id=1 -e "SELECT COUNT(*) AS revenue FROM sales"

https://abcdef12.us1a.app.preset.io/
  revenue
---------
       42

If you don’t specify the database ID you will be shown a list of available databases in order to choose one. If you don’t specify the SQL query via the -e option the CLI will start a simple REPL (read-eval-print loop) where you can run queries interactively.

Synchronizing from exports

You can use the CLI to manage workspaces resources — databases, datasets, charts, and dashboards — from source control. The configuration should be stored as YAML files, using the same format the Apache Superset uses for importing and exporting resources.

The easiest way to generate the configuration files is to build one or more dashboards in a Preset workspace, export them together, and unzip the generated file into a directory.

After unzipping the directory should look like this:

• charts/

• dashboards/

• databases/

• datasets/

• metadata.yaml

You can see an example here.

To synchronize these files to a Preset workspace you only need to run:

% preset-cli --workspaces=https://abcdef12.us1a.app.preset.io/ \
> superset sync native /path/to/directory/

If any of the resources already exist you need to pass the --overwrite flag in order to replace them. The CLI will warn you of any resources that already exist if the flag is not passed:

% preset-cli --workspaces=https://abcdef12.us1a.app.preset.io/ \
> superset sync native /path/to/directory/
Error importing database
The following file(s) already exist. Pass --overwrite to replace them.
- databases/Google_Sheets.yaml
Error importing dataset
The following file(s) already exist. Pass --overwrite to replace them.
- datasets/Google_Sheets/country_cnt.yaml
Error importing chart
The following file(s) already exist. Pass --overwrite to replace them.
- charts/Total_count_134.yaml
Error importing dashboard
The following file(s) already exist. Pass --overwrite to replace them.
- dashboards/White_label_test.yaml

Synchronized resources can be marked as “externally managed” by passing the --disallow-edits flag to the command. When the flag is passed users won’t be able to edit the resources. It’s also possible to provide a URL where the resource can be modified, eg, a link to a file in a Github repository. This can be done by passing the --external-url-prefix flag:

% preset-cli --workspaces=https://abcdef12.us1a.app.preset.io/ \
> superset sync native /path/to/directory/ --disallow-edits \
> --external-url-prefix=https://github.com/org/project/blob/master/

This way, the file dashboards/White_label_test.yaml would have an external URL pointing to https://github.com/org/project/blob/master/dashboards/White_label_test.yaml. Currently the URL is not displayed anywhere, but in the near future we should have affordances pointing users to it from the instance UI.

slice_name: Total sales
viz_type: big_number_total
params:
  metric: sum__sales
  adhoc_filters: []

{% if country %}
slice_name: Sales in {{ country }}
{% else %}
slice_name: Total sales
{% endif %}
viz_type: big_number_total
params:
  metric: sum__sales
  {% if country %}
  adhoc_filters:
    - clause: WHERE
      expressionType: SQL
      sqlExpression: country = '{{ country }}'
      subject: null
      operator: null
      comparator: null
  {% else %}
  adhoc_filters: []
  {% endif %}

% preset-cli --workspaces=https://abcdef12.us1a.app.preset.io/ \
> superset sync native /path/to/directory/ -o country=BR

params:
  metric: sum__sales
  adhoc_filters:
    - clause: WHERE
      expressionType: SQL
      {% if instance.host == '//abcdef12.us1a.app.preset.io/ %}
      sqlExpression: warehouse_id = 1
      {% elif instance.host == '//34567890.us1a.app.preset.io/ %}
      sqlExpression: warehouse_id = 2
      {% else %}
      sqlExpression: warehouse_id = 3
      {% endif %}

database_name: Postgres
sqlalchemy_uri: postgres://{{ env["POSTGRES_HOSTNAME"] }}

# functions/demo.py
def hello_world() -> str:
    return "Hello, world!"

slice_name: {{ functions.demo.hello_world() }}
viz_type: big_number_total
params:
  ...

sql: 'SELECT action, count(*) as times
    FROM logs
    {% if filter_values(''action_type'')|length %}
        WHERE action is null
        {% for action in filter_values(''action_type'') %}
            or action = ''{{ action }}''
        {% endfor %}
    {% endif %}
    GROUP BY action'

sql: 'SELECT action, count(*) as times
    FROM logs
    {{ '{% if' }} filter_values(''action_type'')|length {{ '%}' }}
        WHERE action is null
        {{ '{% for' }} action in filter_values(''action_type'') {{ '%}' }}
            or action = ''{{ '{{' }} action {{ '}}' }}''
        {{ '{% endfor %}' }}
    {{ '{% endif %}' }}
    GROUP BY action'

Synchronizing to and from dbt

The CLI also allows you to synchronize models, and metrics from a dbt project.

If you’re using dbt Core you can point the CLI to your compiled manifest and your profiles file, so that the database is automatically created, together with all the models and metrics. The full command is:

% preset-cli --workspaces=https://abcdef12.us1a.app.preset.io/ \
> superset sync dbt-core /path/to/dbt/my_project/target/manifest.json \
> --project=my_project --target=dev --profiles=${HOME}/.dbt/profiles.yml \
> --exposures=/path/to/dbt/my_project/models/exposures.yaml \
> --import-db \
> --external-url-prefix=http://localhost:8080/

Running this command will:

1. Read the dbt profile and create the $target database for the specified project in the Preset workspace.

2. Every source in the project will be created as a dataset in the Preset workspace.

3. Every model in the project will be created as a dataset in the Preset workspace.

4. Any metrics will be added to the corresponding datasets.

5. Every dashboard built on top of the dbt sources and/or models will be synchronized back to dbt as an exposure.

Descriptions, labels and other metadata is also synced from dbt models to the corresponding fields in the dataset. It’s also possible to specify values for Superset-only fields directly in the model definition, under model.meta.superset.{{field_name}}. For example, to specify the cache timeout for a dataset:

models:
  - name: my_dbt_model
    meta:
      superset:
        cache_timeout: 250 # Setting the dataset cache timeout to 250.

The same is applied for metrics. For example, to specify the d3 format for a metric:

- name: avg_revenue
  label: "AVG Revenue"
  model: ref('my_dbt_model')
  calculation_method: average
  expression: price_each
  timestamp: date
  meta:
    superset:
      d3format: '%d'

The --external-url-prefix should point to your dbt docs, so that the resources in the workspace can point to the source of truth where they are being managed. Similar to the native sync, the dbt sync also supports the --disallow-edits flag.

By default, the CLI sync would create a new database on the destination workspace using below name structure:

f"{project_name}_{target_name}"

If you want to sync data to an existing database connection on the workspace instead, you can specify the database connection name on the profiles YAML file. Add below structure under the <target-name>:

meta:
  superset:
    database_name: my DB name # <= specify the database connection/display name used on the workspace

Example:

jaffle_shop:
  outputs:
    dev:
      meta:
        superset:
          database_name: Postgres - Production

If --import-db was passed and a database connection was found on the workspace, the operation would update the connection configuration with the dbt connection settings.

If you’re using dbt Cloud you can instead pass a job ID and a service account access token:

% preset-cli --workspaces=https://abcdef12.us1a.app.preset.io/ \
> superset sync dbt-cloud \
> $TOKEN $JOB_ID \
> --external-url-prefix=http://localhost:8080/

The token only needs access to the “Metadata only” permission set for your project. You can see the job ID by going to the project URL in dbt Cloud and looking at the last ID in the URL. For example, if the URL is https://cloud.getdbt.com/#/accounts/12345/projects/567890/jobs/ the job ID is 567890.

When syncing from dbt Cloud, the database connection must already exist on the target workspace. The connection display name on the workspace must match the database name from dbt Cloud.

% preset-cli ... --select my_model    # sync only "my_model"
% preset-cli ... --select my_model+   # sync "my_model" and its children
% preset-cli ... --select my_model+2  # sync "my_model" and its children up to 2 degrees
% preset-cli ... --select +my_model   # sync "my_model" and its parents
% preset-cli ... --select +my_model+  # sync "my_model" with parents and children

% preset-cli ... --select my_model --select my_other_model

% preset-cli ... --select my_model+,tag:test

% preset-cli --select my_model+ --exclude tag:test

Exporting resources

The CLI can also be used to export resources (databases, datasets, charts, and dashboards) from a given Preset workspace (using preset-cli) or Superset instance (using superset-cli). This is useful for migrating resources between workspaces, from an existing Superset installation to Preset, or even from Preset to Superset (one of the advantages of Preset is no vendor lock in!).

To export resources from a self-hosted Superset instance:

% superset-cli https://superset.example.org/ export /path/to/directory

This will create a nice directory structure in /path/to/directory, ready to be imported using the sync native command.

To export resources from a Preset workspace:

% preset-cli --workspaces=https://abcdef12.us1a.app.preset.io/ \
> superset export /path/to/directory

It’s also possible to use the CLI to export specific resources:

Use --asset-type to export all assets from a given type. Available options:

• dashboard

• chart

• dataset

• database

For example, running below command would export all dashboards from this workspace (datasets, charts and DB connections wouldn’t be included):

% preset-cli --workspaces=https://abcdef12.us1a.app.preset.io/ \
> superset export /path/to/directory --asset-type=dashboard

Use --asset-ids to filter for specific assets. Available options:

• dashboard-ids

• chart-ids

• dataset-ids

• database-ids

For example, running below command would export specified dashboards from this workspace (datasets, charts and DB connections would be included):

% preset-cli --workspaces=https://abcdef12.us1a.app.preset.io/ \
> superset export /path/to/directory --dashboard-ids=9,10

To import the exported resources into a Preset workspace:

% preset-cli --workspaces=https://abcdef12.us1a.app.preset.io/ \
> superset sync native /path/to/directory

Finally, to import in a standalone Superset instance:

% superset-cli https://superset.example.org/ sync native /path/to/directory

Note that any existing Jinja2 template markers present will be escaped. For example, if you have a virtual dataset defined as:

SELECT action, count(*) as times
FROM logs
WHERE
    action in {{ filter_values('action_type')|where_in }}
GROUP BY action

The resulting YAML file will have the query defined as:

SELECT action, count(*) as times
FROM logs
WHERE
    action in {{ '{{' }} filter_values('action_type')|where_in }} '}}' }}
GROUP BY action

So that when processed by preset-cli superset sync native the original Jinja2 is reconstructed correctly.