Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Prerequisites

None.

Limitations

  • Only those columns that are referenced in this Snap are passed to the output. The rest are dropped. 
Multiexcerpt macro
nameME_ELT_GBQ_StandardSQL
fallbacktrue
  • ELT Snap Pack does not support Legacy SQL dialect of Google BigQuery. We recommend that you use only the BigQuery's Standard SQL dialect in this Snap.


Known Issues

Multiexcerpt include macro
nameME_ELT_KI_Underscore_Refs
pageELT Snap Pack

...

Input/OutputType of ViewNumber of ViewsExamples of Upstream and Downstream SnapsDescription
Input 

Document

  • Min: 1
  • Max: 1
  • ELT Select
  • ELT Transform
The SQL query in which to add the aggregate functions and the optional GROUP BY clause.
Output

Document

  • Min: 1
  • Max: 1
  • ELT Insert-Select
  • ELT Limit

The modified SQL query with the aggregate functions and GROUP BY clause. 

Snap Settings

Info
titleSQL Functions and Expressions for ELT

You can use the SQL Expressions and Functions supported for ELT to define your Snap or Account settings with the Expression symbol = enabled, where available. This list is common to all target CDWs supported. You can also use other expressions/functions that your target CDW supports.


COUNT_IF
  • COUNT_IF
  •  or 
    Parameter NameData TypeDescriptionDefault ValueExample 
    LabelString

    Excerpt Include
    File Writer
    File Writer
    nopaneltrue

    ELT AggregateAggregate Revenue
    Get preview dataCheckbox
    Multiexcerpt include macro
    namegetpreviewdata
    pageELT Intersect
    Not selectedSelected
    General Aggregate Functions List

    This field set enables you to specify the columns for which to add the aggregate functions. Each aggregate function must be specified as a new row. Click to add a row.

    This field set contains the following fields:

    • Aggregate Function
    • Field Name
    • Eliminate duplicates
    • Alias Name
    Aggregate FunctionString/Expression/Suggestion

    Required. Select the aggregate function to use. Click Image Modified to retrieve the list of the supported aggregate functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured. See the Troubleshooting section for details. 

    Alternatively, you can also enter the name of the aggregate function to use; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error. 

    You can use the following aggregate functions:

    • AVG
    • COUNT
    • COUNT_IF
    • MAX
    • MIN
    • SUM

    Additionally, the following aggregate functions are available based on the database type:

    SnowflakeRedshiftAzure Synapse
    • ANY_VALUE
    • BITAND_AGG
    • BITOR_AGG
    • BITXOR_AGG 
    • BOOLAND_AGG 
    • BOOLOR_AGG
    • BOOLXOR_AGG
    • KURTOSIS
    • MEDIAN
    • MODE 
    • SKEW
    • STDDEV
    • STDDEV_POP
    • STDDEV_SAMP
    • VAR_POP
    • VAR_SAMP
    • APPROXIMATE.COUNT
    • BIT_AND 

    • BIT_OR

    • BOOL_AND

    • BOOL_OR

    • MEDIAN
    • STDDEV_POP
    • STDDEV_SAMP
    • VAR_POP
    • VAR_SAMP
    • APPROX_COUNT_DISTINCT
    • COUNT_BIG
    • GROUPING
    • STDEV

    • STDEVP
    • VAR
    • VARP
    Databricks Lakehouse PlatformBigQuery
    • BIT_OR
    • BIT_XOR
    • BOOL_AND
    • BOOL_OR
    • COLLECT_LIST
    • COLLECT_SET
    • EVERY
    • FIRST_VALUE_RESPECT_NULLS
    • FIRST_VALUE_IGNORE_NULLS
    • LAST_VALUE_RESPECT_NULLS
    • LAST_VALUE_IGNORE_NULLS
    • SKEWNESS
    • STDDEV_POP
    • STDDEV_SAMP
    • VAR_POP
    • VAR_SAMP
    • ANY_VALUE
    • BIT_AND
    • BIT_OR
    • BIT_XOR 
    • LOGICAL_AND
    • LOGICAL_OR

    See Snowflake Aggregate Functions, Redshift SQL Functions Reference, Aggregate Functions (Transact-SQL),

     Databricks Built-in Functions Reference, or BigQuery Aggregate Functions in Standard SQL for more information on the respective aggregate function.

    N/AAVG
    Field NameString/ExpressionRequired. Select the column on which to apply the aggregate function.N/AREVENUE
    Eliminate duplicatesCheck box

    Select to apply DISTINCT to the column specified in the Field field. This means that the aggregate function is applied only to the unique values in the column. 

    Note
    titleBehavior with COUNT_IF aggregate function

    Selecting this checkbox while using COUNT_IF aggregate function does not eliminate duplicate records in case of Snowflake and BigQuery databases, as there is no native support. However, it eliminates the duplicates from the list of records when used with a Redshift, Azure Synapse, or Databricks Lakehouse Platform (DLP) instance.


    Not selectedSelected
    Alias NameStringRequired. Specify the column in which to display the result of the aggregate function. You can also reference this name in downstream Snaps to process the data further. N/ATOTAL_REVENUE
    Linear Regression Aggregate Functions List 

    This field set enables you to specify the columns for which to apply the linear regression aggregate functions.  Each function must be specified as a new row. Click  to add a row.

    This field set contains the following fields:

    • Aggregate Function
    • First Field
    • Second Field
    • Alias Name
    Aggregate FunctionString/Expression/Suggestion

    Required. Select the aggregate function to use. Click  to retrieve the list of the supported linear regression aggregate functions. The list displays the functions supported by the database. An error is displayed if an account is not configured. See the Troubleshooting section for details. 

    Alternatively, you can also enter the name of an aggregate function; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error. 

    You can use the following aggregate functions:

    • CORR
    • COVAR._POP
    • COVAR._SAMP
    • REGR._AVGX
    • REGR._AVGY
    • REGR._COUNT
    • REGR._INTERCEPT
    • REGR._R2
    • REGR._SLOPE
    • REGR._SXX
    • REGR._SXY
    • REGR._SYY
    • MINHASH (for Snowflake only)
    • OBJECT._AGG (for Snowflake only)

    See Snowflake Aggregate Functions or Databricks Built-in Functions Reference, or BigQuery Aggregate Functions in Standard SQL for more information on the respective aggregate function.

    Info
    titleThough not supported natively

    BigQuery does not natively support the above list of Linear Regression Aggregate Functions. However, SnapLogic provides you with the ability to use these functions with BigQuery through a series of internal query rewrites.


    N/ACORR
    First Field
    String/ExpressionRequired. Specify the first column on which to apply the linear regression aggregate function.N/AREVENUE_LOC1

    Second Field

    String/ExpressionRequired. Specify the second column on which to apply the linear regression aggregate function.N/AREVENUE_LOC2
    Alias NameStringRequired. Specify the column in which to display the result of the aggregate function. You can also reference this name in downstream Snaps to process the data further.N/ALINREGAGG_REVENUE
    Aggregate Concatenation Functions List (Not valid for Databricks Lakehouse Platform)

    This field set enables you to specify the list of aggregate concatenation functions to be used for aggregation. The list displays the functions supported by the database that you select in the Account settings. Each function must be specified as a new row. Click  to add a row.

    This field set contains the following fields:

    • Aggregate Concatenation Function
    • Field Name
    • Alias Name
    • Delimiter

    Aggregate Concatenation Function

    String/Expression/Suggestion

    Required. Select the aggregate concatenation function to use. Click  to retrieve the list of the supported functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured. See the Troubleshooting section for details. 

    Alternatively, you can also enter the name of the aggregate concatenation function to use; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error. 

    You can use the following aggregate functions:

    SnowflakeRedshiftAzure Synapse
    • LISTAGG
    • LISTAGG_DISTINCT
    • ARRAY_AGG
    • ARRAY_AGG_DISTINCT
    • LISTAGG
    • DISTINCT_LISTAGG
    • STRING_AGG
    BigQuery

    • ARRAY_AGG
    • ARRAY_AGG_DISTINCT
    • ARRAY_CONCAT_AGG
    • STRING_AGG
    • STRING_AGG_DISTINCT


    See Snowflake Aggregate Functions, Redshift SQL Functions Reference or Aggregate Functions (Transact-SQL), or BigQuery Aggregate Functions in Standard SQL for more information on the respective aggregate function.

    N/ALISTAGG

    Field Name

    String/ExpressionRequired. Specify the column name to apply the concatenation function.N/ANEW_LOCATIONS

    Alias Name

    StringRequired. Specify the column in which to display the result of the concatenation function. You can also reference this name in downstream Snaps to process the data further.N/AUNIQUE_LOCS_LIST

    Delimiter

    String/ExpressionSpecify the delimiting character (string constant) to be used to separate the concatenated values., (comma); (semi-colon)
    Percentile Distribution Functions List (Not valid for Azure Synapse and , Databricks Lakehouse Platform, BigQuery)

    This field set enables you to specify the list of percentile distribution functions to be used for aggregation. The list displays the functions supported by the database that you select in the Account settings. Each function must be specified as a new row. Click  to add a row.

    This field set contains the following fields:

    • Percentile Distribution Function
    • Percentile
    • Alias Name

    Percentile Distribution Function

    String/Expression/Suggestion

    Required. Select the percentile distribution function to use. Click  to retrieve the list of the supported functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured. See the Troubleshooting section for details. 

    Alternatively, you can also enter the name of the percentile distribution function to use; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error. 

    You can use the following aggregate functions:

    SnowflakeRedshiftSynapse
    • PERCENTILE_DISC
    • PERCENTILE_CONT
    • APPROXIMATE_PERCENTILE_DISC
    • PERCENTILE_CONT

    None

    See Snowflake Aggregate Functions or Redshift SQL Functions Reference for more information on the respective aggregate function.

    N/A

    PERCENTILE_DISC

    Percentile String/ExpressionRequired. Specify the percentile value to be considered for applying the percentile distribution function.N/A0.8 (80th percentile)
    Alias NameStringRequired. Specify the column in which to display the result of the percentile distribution function. You can also reference this name in downstream Snaps to process the data further.N/AREVENUE_CPERCENTILE
    GROUP BY Fields List

    This field set enables you to specify the columns for which to use the GROUP BY clause. Each column must be specified in a new row. Click + to add a row.

    This field set contains the following field:

    • GROUP BY Field
    GROUP BY FieldString/eXPRESSIONExpressionSpecify the column in which to add the GROUP BY clause.N/A

    GRADE

    GENDER

    ORDER-BY Fields (Aggregate Concatenation Functions Only)

    This field set enables you to specify the columns by which to sort the output data set. Each column must be specified in a new row. Click + to add a row.

    This field set contains the following fields:

    • ORDER BY Field
    • Sort Order
    • Null Value Sort Preference
    ORDER BY FieldString/ExpressionSpecify the column by which to sort the output data set.N/A

    GRADE

    GENDER

    Sort OrderDrop-down listChoose one of the possible sort orders - ASC (ascending) or DESC (descending) for the output data set.ASCDESC
    Null Value Sort PreferenceDrop-down listChoose where in the sort order do the null values, if any, in the ORDER BY Field be placed - NULLS FIRST (at the beginning) or NULLS LAST (at the end)NULLS FIRSTNULLS LAST

    ...

    We want to perform aggregate functions for the values in the BYTE column. Accordingly, we add the ELT Aggregate Snap and configure it as required. In this example, we want to calculate the COUNT, SUM, MIN, and MAX. So, we configure the ELT Aggregate Snap as shown below:

    Image RemovedImage Added

    Based on this configuration, the ELT Aggregate Snap builds a query as shown below:

    ...