Skip to main content
Version: 3.0.0

Writing to a Relational Database

Prerequisites​

  • A Connection to the Database system you wish to write to
  • An upstream Component with data to write

Create the file​

Navigate to your Workspace from the Homepage

Within the File Explorer, navigate to the Flow with the Component you wish to write out

Create a new file and give it a name with a .yaml extension, e.g. db_write.yaml

my_project/
├── ascend_project.yaml
├── connections/
│   ├── mysql_wc.yaml
│   └── bigquery_wc.yaml
├── flows/
│   ├── bar_flow/
│   │   ├── bar_flow.yaml
│   │   └── components/
│   │       └── bar_component.yaml
│   └── foo_flow/
│       ├── foo_flow.yaml
│       └── components/
│           ├── foo_component.yaml
│           └── (+ New File) db_write.yaml
├── profiles/
└── vaults/

Write Options​

  • connection The name of the Ascend connection that will be used to write to the database, the connection will need write permissions on the target database

  • input Specifies the input Component that will be written to the database

  • <write_connector_target> The name of the option itself defines the type of write connector that will be created, and different write connectors will require different options to specify the target table. For a full list of write connector types, see Write Connectors

  • strategy can be one of four options:

    • snapshot will append a complete snapshot of the upstream Component to the target table, preserving a full copy of each previous state of the input Component.

    • full performs a full refresh of the target table, replacing all of the records each Flow Run. Currently only drop_and_recreate mode is supported for full writes.

    • incremental writes only the different records between the current and previous Flow Run to the target table. Must specify one of append, upsert, or sync for the mode to determine how data is added to the target table. For a full explanation of the incremental write strategies see Incremental Write Strategies

    • partitioned writes only the partitions on the target table where records have been updated in the input Component. Must specify one of append, insert_overwrite, or sync for the mode to determine how partitions are written to the target table. For a full explanation of the partitioned write strategies see Partitioned Write Strategies

Examples​

my_project/flows/foo_flow/components/db_write.yaml
component:
  write:
    connection: bigquery_wc
    input:
      name: foo_component
      flow: foo_flow
    strategy:
      partitioned:
        mode: sync
    bigquery:
      table:
        name: my_partitioned_bq_table
        dataset: my_output_dataset

In this example, we are using a BigQuery Connection to write out the contents of foo_component to a BigQuery table, with a partitioned write strategy, using the sync mode to replace only the partitions that have been updated in foo_component since the last Flow Run.

my_project/flows/bar_flow/components/db_write.yaml
component:
  write:
    connection: mysql_wc
    input:
      name: bar_component
      flow: bar_flow
    strategy:
      full:
        mode: drop_and_recreate
    mysql:
      table:
        name: my_output_table

In this example, we are using a MySQL Connection to write out the contents of bar_component (which is in a different flow) to a MySQL table, with a full write strategy, using the drop_and_recreate mode to replace the entire table each Flow Run.

Last updated on