Table configuration

When you activate a table, its Configure Table modal opens so you can configure its settings, which are inherited by metrics based on the table. You can override these settings on a per-metric basis as needed.

Note that these settings are not inherited by SQL metrics. With a SQL metric, you manage its settings via a combination of configuration options and SQL.

Data Collection settings

The Data Collection settings you can select depend on how you set Query Scope, which can only be set for a table when you first configure it— you can't change this setting after you save the table's configuration. However, if you override inherited table settings in a metric, you can change the Query Scope for the metric.

If Query Scope is set to Incremental, your form will have the fields shown below:

If Query Scope is set to Full Table, your form will instead have the fields shown below.

The various Data Collection settings are described on the following pages:

Partitions

If Lightup detects that your table has date-based partitions, you'll see an additional Partitions section in your form. Use this section to specify the partition columns and the format of each partition. By specifying a partition, you will be enabling Lightup to generate efficient queries that query only partitions where the data is expected to reside. Not all datasources support partitioning— check the relevant connector subpage for partition support information.

Partitions can be complex. For a general overview, see Partitions. Another good way to understand partitions better is to experiment with the partition settings on a simple metric with overridden Data Collection settings. Run previews and then look at the query history. Some examples are included below.

Example: table with one partition

Example: table with three partitions

Add a partition

  1. To add a partition column, click Add + and then select the column from the drop-down list. Partition columns are labelled (partition) in the list.
  2. Specify the partition's format using Python format codes. When Lightup generates a query for a time interval, it will convert the start and end of that time interval to the partition date format and generate a where clause that queries only the partitions where the data is expected to reside. Consider the following examples to better understand how partition formats work.
    • Example 1: If you have a partition column called month and it contains a two-digit month number, you can enter %m for the format. If Lightup is querying the date range 03/07/2023 through 03/13/2023, the following WHERE clause will be included in the query:
      WHERE `month` in (03)
      
    • Example 2: If you have three partition columns called year (a four-digit year), month (a two-digit month), and day (a two-digit day), you can enter %Y for year, %m for month, and %d for day. If Lightup is querying the date range 03/07/2023 through 03/13/2023, the following WHERE clause will be included in the query:
      WHERE
      			`year` in (2023)
      			AND `month` in (03)
      			AND `day` in (07, 08, 09, 10, 11, 12, 13)
      
    • Example 3: If you have a partition column called datehour that contains the date and hour, in a format such as 03/07/2023/18, you can enter %y/%m/%d/%H for the format. If Lightup is querying the time range 03/07/2023 18:00 through 03/08/2023 18:00, the following WHERE clause will be included in the query:
      WHERE
      		CAST(`datehour` AS STRING) in (
      				'2023/03/07/18',
      				'2023/03/07/19'
              ...
              '2023/03/07/23',
              '2023/03/08/00',
              '2023/03/08/01',
              ...
              '2023/03/08/18'
      			)
      
  3. Click Advanced to configure the Timezone, Partition Offset Past and Partition Offset Future settings for partitions. Partition Offset Past and Partition Offset Future let you add partition offsets to tell Lightup to query adjacent partitions associated with timestamps in the past or future.
    • In Example 2 above (with year, month, and day partitions), if Partition Offset Past is set to 2 days, the WHERE clause will include two additional days in the past (05 and 06):
      WHERE
      			`year` in (2023)
      			AND `month` in (03)
      			AND `day` in (05, 06, 07, 08, 09, 10, 11, 12, 13)
      
    • In Example 3 above (with a partition column called datehour), if Partition Offset Past is set to 2 days in the past, the WHERE clause will include the full two-day period, hour by hour for the two days prior to the query period, resulting in:
      WHERE
      		CAST(`datehour` AS STRING) in (
              '2023/03/05/18',
              ...
              '2023/03/05/23',
              '2023/03/06/00',
              ...
              '2023/03/06/23',
              '2023/03/07/00',
              ...
      				'2023/03/07/18',
      				'2023/03/07/19'
              ...
              '2023/03/07/23',
              '2023/03/08/00',
              '2023/03/08/01',
              ...
              '2023/03/08/18'
      			)