Skip to main content

Database Sources - Table Mode & Query Mode

Nexla's database connectors provide self-serve capabilities to serve any use case ranging from simple to complex. One feature supporting these varied use cases is the ability to set up a database source—for example, a Google BigQuery or Databricks source—using Table Mode or Query Mode.

This article explains the features available in Table Mode and Query Mode and how to use each when setting up a data source with a database connector.

1. Table Mode​

Table Mode allows users to specify the database source through a simple selection method. This mode is equivalent to running a simple, optimized SELECT operation on any database table, but it also provides additional customization options to filter which rows will be read by Nexla.

When setting up a database source, after selecting the source type and credentials, follow the below instructions to configure the database source using Table Mode. Section 1.1 provides instructions for selecting the database table that will serve as the data source, and Section 1.2 covers data-filtering options.

1.1 Table Selection​

  1. In the Configure2.png screen, ensure that the Table Mode tab is selected.

      TableMode.png

  2. The Table Mode tab displays a directory of all available tables in the source database. In this directory, find the table from which Nexla should read data.

    To expand a location in the database directory, click the Expand.png icon next to it.

      Location3.png

    1. Optional: To preview the columns that a table contains, click Expand.png next to the table name.   Table2.png

  3. Select the table from which Nexla should read data by hovering over it and clicking the Select.png button that appears to the right.

    Once the table is selected, the button will display Selected.png, and the path of the selected table will be shown above the directory list.

      Table2.png

  4. Optional: Click the Test.png button in the upper right corner of the Table Mode tab to generate a preview sample of data from the selected table at the bottom of the screen.

      SampleData.png

    • To create a filter specifying which data Nexla should read from this source, continue to Section 1.2.

    • To finish creating this source without a filter, configure any desired scheduling options, and click Create.png in the top right corner of the screen.

    For more information about configuring source scan scheduling options, see the documentation for the specific source connector type on the Connectors page.

1.2 Data-Filtering Options​

For a database source, the default configuration instructs Nexla to read all data in the selected table during each ingestion cycle—this is equivalent to running a SELECT clause on the table. However, if the source contains historical data that should not be scanned, Nexla provides the option to select different table scan modes that specify which data should be ingested.

To apply a data filter, select an option from the Table Scan Mode pulldown menu in the Advanced Settings panel on the right. Each available table scan mode is explained below.

  TableScanMode.png

  • Read the whole table: This is the default configuration for a database source; it instructs Nexla to scan the entire table.

    • This option is selected by default when creating a new database source, and no additional action or information is required to use this mode.

        WholeTable.png

  • Start reading from a specific ID: This option configures Nexla to begin scanning the table at a specific ID stored in a numeric column in the table.

    1. Select "Start reading from a specific ID" from the Table Scan Mode pulldown menu.

        ID.png

    2. Select the table column containing the IDs that will be used to specify the ID from which Nexla should begin reading data.

      The selected column must be a numeric column.

        ID_Column.png

    3. Enter the ID at which Nexla should start ingesting data from the table in the Starting ID field.

        StartingID.png

    4. Configure any desired scheduling options, and then click Create.png in the top right corner of the screen to finish creating this source with the applied filter.

  • Start reading from a specific ID and timestamp: This option configures Nexla to begin scanning the table at a location specified by both an ID and a timestamp.

    1. Select "Start reading from a specific ID and timestamp" from the Table Scan Mode pulldown menu.

        ID_Timestamp.png

    2. Select the table column containing the IDs that will be used to specify the ID from which Nexla should begin reading data.

      The selected column must be a numeric column.

        ID_Column2.png

    3. Enter the ID at which Nexla should start ingesting data from the table in the Starting ID field.

        StartingID2.png

    4. Select the column containing the timestamp information that will be used to specify the timestamp from which Nexla should begin reading data.

      The selected column must be a datetime column.

        TimestampColumn.png

    5. Enter the timestamp at which Nexla should start ingesting data from the table in the Starting Timestamp field.

      The timestamp entered in this field must be a value in UNIX epoch or ISO format.

        Timestamp.png

    6. Configure any desired scheduling options, and then click Create.png in the top right corner of the screen to finish creating this source with the applied filter.

  • Start reading from a specific timestamp: This option configures Nexla to begin scanning the table at a specific timestamp stored in a datetime column in the table.

    1. Select "Start reading from a specific timestamp" from the Table Scan Mode pulldown menu.

        TimestampMode.png

    2. Select the column containing the timestamp information that will be used to specify the timestamp from which Nexla should begin reading data.

      The selected column must be a datetime column.

        TimestampColumn2.png

    3. Enter the timestamp at which Nexla should start ingesting data from the table in the Starting Timestamp field.

      The timestamp entered in this field must be a value in UNIX epoch or ISO format.

        Timestamp2.png

    4. Configure any desired scheduling options, and then click Create.png in the top right corner of the screen to finish creating this source with the applied filter.

2. Query Mode​

Query Mode allows users to perform a query of any complexity level to specify the database source. This mode provides a free-form query editor that can be used to perform any query written using the syntax and convention supported by the underlying database and/or warehouse.

When setting up a database source, after selecting the source type and credentials, follow the below instructions to configure the database source using Query Mode. Section 2.1 provides instructions for constructing a database query to configure data ingestion from the source, and Section 2.2 covers database commit options.

2.1 Write the Custom Query​

  1. In the Configure2.png screen, ensure that the Query Mode tab is selected.

      QueryMode.png

  2. Using the syntax and convention supported by the underlying database, construct the query specifying which data Nexla should ingest in the Custom Query to Fetch Data field.

    Nexla supports any query that can be written following the syntax and convention of the underlying database, regardless of complexity.

      QueryField.png

    • Information about the appropriate query syntax and convention can be found in the documentation published by the underlying database (Databricks, Google BigQuery, Snowflake, etc.).

    • For assistance with constructing a custom query, contact support@nexla.com.

  3. Optional: Click the Test.png button in the upper right corner of the Query Mode tab to generate a preview sample of the data that will be retrieved according to the constructed query. This data preview will appear at the bottom of the screen.

      QueryTest.png

    • To configure Nexla to perform a database commit after reading data from this source, continue to Section 2.2.

    • To finish creating this source without a database commit, configure any desired scheduling options, and click Create.png in the top right corner of the screen.

    For more information about configuring source scan scheduling options, see the documentation for the specific source connector type on the Connectors page.

2.2 Database Commit Options​

Some custom queries can contain statements that should be committed to the database source after data ingestion. For example, when a query includes data manipulation operations such as INSERT, DELETE, or UPDATE transactions, a COMMIT operation must be performed to save the associated changes in the source database and make them visible to other database users. In these cases, Nexla can easily be configured to execute a database COMMIT at the end of data ingestion.

Typically, queries created in Nexla using Query Mode do not require a COMMIT. Database changes made by executing a COMMIT are permanent; thus, this option should be utilized with care to avoid database record and/or relationship losses.

  1. To configure Nexla to execute a database COMMIT at the end of the query, select "True" from the Perform Database Commit After Read? pulldown menu in the Advanced Settings panel on the right side of the screen.

      DatabaseCommit.png

    • Since changes made to the data, table structure, etc. using a COMMIT operation are permanent, please ensure that the constructed query is sequentially and logically correct when Nexla is configured to perform a database COMMIT after reading data from the source.

  2. Configure any desired scheduling options, and then click Create.png in the top right corner of the screen to finish creating this source with the selected database COMMIT option.