Creating Native SQL based query¶

Functions in Native SQL Query¶

• Most of the SQL keywords are supported except ANY, ALL, TOP and SELECT *, COUNT(*).

Getting Started¶

Fetching schema details¶

This section describes how to get the necessary information from Data Contextualization to create queries.

Retrieving Schemas to write physical queries¶

Input depends upon the schemas to be considered for fetching the data.

Use the following endpoint:

POST api/sdi/v4/searchSchemas

Define the following header:

Authorization: {Bearer Token}
Content-Type: application/json

Request example¶

{
  "schemas": [
    {
      "schemaName": "nhtsa_make"
    }
  ]
}

Response example¶

{
  "id": "5e4c461c577c210e17e64357",
  "originalFileNames": ["vehiclemake.csv"],
  "schemaName": "nhtsa_make",
  "schemaDescription": "",
  "dataRegistryId": "C159F0FFED3B787C283AC467424E40E1",
  "schemaVersion": 0,
  "latestSchemaVersion": 1,
  "createdAt": 1582056988262,
  "lastUpdated": 1582056988286,
  "schema": {
    "def": {
      "dataType": "string",
      "customTypes": [
        "us_state",
        "partnumber1581984706",
        "partnumber1",
        "partnumber1582056922"
      ]
    },
    "id": {
      "dataType": "integer"
    }
  },
  "storageLocation": [
    {
      "locationType": "s3",
      "location": "s3://parquet-file-storage-dev/ediint2/nhtsa_make",
      "dataType": "parquet"
    }
  ],
  "metadata": {
    "versionSuffix": "v",
    "checksum": "2F9A11BA607CC9E4EA9A90AD23A3BAB2",
    "pointsToVersion": 1
  },
  "dataFolderSize": ""
}

Create native query¶

Detailed description of create query request parameters¶

The rules to be followed during query creation are as follows:

1. Encode sqlStatement using base64 encoder before adding to the request.

2. If the column name used in the query contains a special character, then enclose it with backtick `.

Limitations in query creation¶

• Only select queries are supported.
• Currently the following SQL functions are not supported: TOP, ANY, ALL, SELECT *, COUNT(*)
• While creating registry, user should not use dot . in source name or tag, since schema name with dot is not supported in current version.
• If schema name or column name is a SQL keyword then user needs to surround name with backtick ` symbol.
• A user needs to use alias in the SQL query for the ifnull function to get correct results.
• The values provided in the static queries should be matched with data types of corresponding attribute. Otherwise, the system will not validate this during query create/update process.
• The values provided in the parameters for dynamic queries should be matched with datatype of corresponding attribute. Otherwise, the system will not validate this during query execution request.
• If a query contains having clause in the main query and/or sub query, then the exact same expression (column name or aggregate function on column) should be present in SELECT clause along with other select attributes. e.g.
  1. SELECT COUNT(Customers.CustomerID) FROM Customers HAVING COUNT(Customers.CustomerID) > 5
  2. SELECT Customers.CustomerID FROM Customers HAVING Customers.CustomerID > 5
• If a query contains order by clause in the main query and/or sub query, then the column name on which order is applied should be present in the select clause along with other select attributes. e.g.
  1. SELECT Customers.CustomerID FROM Customers ORDER BY COUNT(Customers.CustomerID)
  2. SELECT Customers.CustomerID FROM Customers ORDER BY Customers.CustomerID
• While updating a query,
  1. a physical query cannot be updated to business query and vice versa.
  2. a static query cannot be updated to dynamic query and vice versa.
• Business Query limitations:
  1. User should always pass column name in {class name}.{property name} format.
  2. Alias should not be given to class and/or class property in query at the time of query creation, but by setting isDynamic attribute true, user can provide alias to attributes in select clause at the time of query execution.

Query details¶

• Physical Query

  The query created using schemas and its properties present in registry is called a physical query. The user is allowed to form a valid SQL statement using schema and its properties.

• Business Query

  The query created using classes and its properties present in ontology is called a business query. The user is allowed to form a valid SQL statement using class or classes and its properties.

  Business queries use underlying mappings provided in the semantic models to query data from multiple sources of data.

Queries (both Physical and Business) in Data Contextualization can be Static or Dynamic in type.

• Static Query (isDynamic = false): A query can contain parameters and/or aliases. But, if the values of parameters are defined at the time of creation, then it is considered as static query having isDynamic flag set to false. The value of any parameter cannot be modified at the time of execution.

  For example:

  1. SELECT sditest_vehicle.vin FROM sditest_vehicle
  2. SELECT sditest_vehicle.vin FROM sditest_vehicle WHERE sditest_vehicle.vin = 1

  The user cannot create execution job for static query as the system creates it and initiates the execution. The user can get the system created execution job Id using get query by id API /queries/{id} under lastTenExecutionJobIds attribute.

  To retrieve result of execution, call the /queries/{id}/executionJobs/latestResults API.

• Dynamic Query (isDynamic = true): A dynamic query contains parameters of which value can provided at the time of execution or alias/es for column name/s in select clause.

  Set isDynamic flag to true while creating dynamic queries.

  Dynamic queries help users to create multiple execution requests on a single query. For example, if user wants to create a dashboard that exhibits information about plant performance from different locations. Then app developers can create queries to provide same data attributes for different plants and value of location can be sent dynamically over a different execution requests.

  This helps users to dynamically provide range of filter values on which correlated queried data results can be consumed. Further, using standard Native SQL query functions different aggregate functions can be used to create different KPIs analytics and will support variety of use cases.

  Dynamic queries can be roughly categorized in three types -

  • parameterized query
  • non-parameterized queries with aliases
  • parameterized queries with aliases

For more details on how to create execution Jobs, refer How to create executionJobs for dynamic query.

Parameterized query¶

A query can have parameters in where clause or having clause. A parameterized query is supported where a user can provide a placeholder for column value and provide actual value at the time of execution.

Each execution job can have different set of values for placeholders in a query. Datatype of placeholder value should match with column datatype. The placeholder name should be same as column name and it needs to be provided in :"{column name}" format.

• If the column name is provided in the format {schema name}.{column name} then, the placeholder value should be :"{schema name}.{column name}"
• If the column name is provided in the format {column name} then, the placeholder value should be :"{column name}"
• If the column name contains special characters, then column name and placeholder should be enclosed with backticks `.

For example:

1. SELECT sditest_vehicle.vin FROM sditest_vehicle WHERE sditest_vehicle.vin = :"sditest_vehicle.vin" AND sditest_vehicle.def = 'BMW'. Here, sditest_vehicle.vin is a placeholder of which the value can be provided at the time of execution job creation. The value of column sditest_vehicle.vin is provided at the time of creation itself.
2. SELECT vin FROM sditest_vehicle WHERE vin = :"vin" AND def = 'BMW' Here, vin is a placeholder of which the value can be provided at the time of execution job creation. The value of column vin is provided at the time of creation itself.
3. SELECT sditest_vehicle.`vi@n` FROM sditest_vehicle WHERE sditest_vehicle.vi@n = :"sditest_vehicle.vi@n" AND sditest_vehicle.def = :sditest_vehicle.def" Here, the column name `vi@n` contains special character and therefore it is enclosed with backtick `. The value of column(s) needs to be provided at the time of job creation.

Query having aliases¶

A query can have aliases for select attributes (aliases can only be provided for column present in the main select clause not in sub query).

When we execute a query, the result contains column names if the alias is not provided. But, a user can have a business requirement where it is required to get a result having different name (alias) for the result column.

A functionality is provided wherein the user can give different display name (alias) to the result columns at the time of execution job creation.

For example:

• SELECT sditest_vehicle.vin FROM sditest_vehicle WHERE sditest_vehicle.vin = 1

  Here, the query does not contain parameters, but the user can provide alias for select clause attributes at the time of execution.

• SELECT sditest_vehicle.vin AS Vin FROM sditest_vehicle

  Here, the user has already provided an alias for column name but, it is still possible to provide different alias at the time of execution job creation.

Parameterized query having aliases¶

A parameterized query can also have alias. In this case, the user needs to provide a value of parameter as well as alias at the time of execution job creation.

Below given are few examples of dynamic queries:

• sqlStatement: SELECT sditest_vehicle.vin FROM sditest_vehicle WHERE sditest_vehicle.vin = :"sditest_vehicle.vin" AND sditest_vehicle.def = 'BMW'

  Execution job request:

  {
    "description": "Running query with sample alias and parameters",
    "parameters": [
      {
        "paramName": "sditest_vehicle.vin",
        "paramValue": "'abc'"
      }
    ]
  }
  

• sqlStatement: SELECT sditest_vehicle.vin FROM sditest_vehicle WHERE sditest_vehicle.vin = :"sditest_vehicle.vin" AND sditest_vehicle.def = :"sditest_vehicle.def"

  Execution job request:

  {
    "description": "Running query with sample alias and parameters",
    "parameters": [
      {
        "paramName": "sditest_vehicle.vin",
        "paramValue": "'abc'"
      },
      {
        "paramName": "sditest_vehicle.def",
        "paramValue": "'BMW'"
      }
    ]
  }
  

• sqlStatement: SELECT sditest_vehicle.def FROM sditest_vehicle WHERE sditest_vehicle.vin = 1

  Execution job request:

  {
    "description": "Running query with sample alias and parameters",
    "aliases": [
      {
        "attributeName": "sditest_vehicle.def",
        "aliasValue": "Def"
      }
    ]
  }
  

• sqlStatement : SELECT sditest_vehicle.vin FROM sditest_vehicle WHERE sditest_vehicle.vin = sditest_vehicle.vin" AND sditest_vehicle.def = 'BMW'

  Execution job request:

  {
    "description": "Running query with sample alias and parameters",
    "parameters": [
      {
        "paramName": "sditest_vehicle.vin",
        "paramValue": "'abc'"
      }
    ],
    "aliases": [
      {
        "attributeName": "sditest_vehicle.vin",
        "aliasValue": "Vin"
      }
    ]
  }
  

• sqlStatement: SELECT COUNT(sditest_vehicle.def) FROM sditest_vehicle WHERE sditest_vehicle.vin = 1

  Execution job request :

  {
    "description": "Running query with sample alias and parameters",
    "aliases": [
      {
        "attributeName": "COUNT(sditest_vehicle.def)",
        "aliasValue": "Def"
      }
    ]
  }
  

To execute a dynamic query, the user needs to create a execution job (using /queries/{id}/executionJobs) and to retrieve results, the user needs to call /executionJobs/{id}/results API.

The user can also use /queries/{id}/executionJobs/latestResults API to get result of latest execution job.

A dynamic query can have multiple execution jobs.

Query having resultType specified¶

• While creating a query, user can provide the "resultType" parameter to indicate how the results of the query should be returned.
• The possible values for "resultType" are REST and FILE. REST is the default value for this parameter.
• When "resultType" is set to REST, Data Contextualization returns the results of the query execution as a REST API response.
• When "resultType" is set to FILE, Data Contextualization creates file(s) in the IDL for results of this query. The "resultDetail" parameter can be used to provide additional input to the API such as the desired file type and the desired results location in the IDL.
• For static queries, the "resultType" must be set at the time of query creation.
• For dynamic queries, user can specify the "resultType" at the time of query creation or also at the time of creating a query execution. When both values are present, the "resultType" set on the query execution decides how the query results are returned.

{
  "description": "file result",
  "isBusinessQuery": false,
  "isDynamic": false,
  "name": "Test mdl BQ 4th subQ, 2P",
  "sqlStatement": "{{ encodedSqlStatement }}",
  "resultType": "REST"
}

{
  "description": "file result",
  "isBusinessQuery": false,
  "isDynamic": true,
  "name": "Test mdl BQ 4th subQ, 2P",
  "sqlStatement": "{{ encodedSqlStatement }}",
  "resultType": "FILE",
  "resultDetail": {
    "fileType": "parquet",
    "location": "/sdiresults/"
  }
}

Limitation:

Max result rows in result can be 1000000 rows or 250 MB, whichever limit occurs first system will return it.

Sample result:

{
  "status": "CURRENT",
  "timestamp": "2022-04-14T11:24:16.221Z",
  "resultType": "FILE",
  "data": [],
  "resultDetail": {
    "fileType": "parquet",
    "basePath": "/data/ten=ediinteg",
    "location": "/sdiresults/6258046083111b0bb5226e98/6258046083111b0bb5226e99/2022-04-14T11:24:16.221Z"
  }
}

Examples¶

Select attributes from a schema¶

Schema name: sditest_vehicle

The query created by a user is as given below:

SELECT sditest_vehicle.vin FROM sditest_vehicle

To add this query to create request, we need to encode it using base64 encoder.

c2VsZWN0IHNkaXRlc3RfdmVoaWNsZS52aW4gZnJvbSBzZGl0ZXN0X3ZlaGljbGU=

The query does not contain any parameters and the user is not willing to provide any alias at runtime.

isDynamic = false

The query is created using schema and its properties

isBusinessQuery = false

Create query request JSON:

{
  "description": "Test Query",
  "isDynamic": false,
  "isBusinessQuery": false,
  "name": "crashanalysis",
  "sqlStatement": "c2VsZWN0IHNkaXRlc3RfdmVoaWNsZS52aW4gZnJvbSBzZGl0ZXN0X3ZlaGljbGU="
}

Query with where clause¶

Schema name: sditest_vehicle

The query created by a user is as given below:

SELECT sditest_vehicle.vin FROM sditest_vehicle sditest_vehicle.def = 'Car'

To add this query to create request, we need to encode it using base64 encoder.

c2VsZWN0IHNkaXRlc3RfdmVoaWNsZS52aW4gZnJvbSBzZGl0ZXN0X3ZlaGljbGUgc2RpdGVzdF92ZWhpY2xlLmRlZiA9ICdDYXIn

The query does not contain parameters but values are defined and user is not willing to provide any alias at runtime.

isDynamic = false

The query is created using schema and its properties.

isBusinessQuery = false

Create query request JSON:

{
  "description": "Test Query",
  "isDynamic": false,
  "isBusinessQuery": false,
  "name": "crashanalysis",
  "sqlStatement": "c2VsZWN0IHNkaXRlc3RfdmVoaWNsZS52aW4gZnJvbSBzZGl0ZXN0X3ZlaGljbGUgc2RpdGVzdF92ZWhpY2xlLmRlZiA9ICdDYXIn"
}

Select few attributes from a schema with alias¶

The query created by a user is as given below:

SELECT sditest_vehicle.vin AS Vin FROM sditest_vehicle

To add this query to create request, we need to encode it using base64 encoder.

c2VsZWN0IHNkaXRlc3RfdmVoaWNsZS52aW4gYXMgVmluIGZyb20gc2RpdGVzdF92ZWhpY2xl

The query does not contain parameters, alias for a column name is provided at the time of query creation only and user is not willing to provide any alias at runtime.

isDynamic = false

The query is created using schema and its properties.

isBusinessQuery = false

Create query request JSON:

{
  "description": "Test Query",
  "isDynamic": false,
  "isBusinessQuery": false,
  "name": "crashanalysis",
  "sqlStatement": "c2VsZWN0IHNkaXRlc3RfdmVoaWNsZS52aW4gYXMgVmluIGZyb20gc2RpdGVzdF92ZWhpY2xl"
}

Select few attributes from a schema with dynamic parameters¶

The query created by a user is as given below:

SELECT sditest_vehicle.vin FROM sditest_vehicle WHERE sditest_vehicle.vin= :"sditest_vehicle.vin" AND sditest_vehicle.def = 'BMW'

To add this query to create request, we need to encode it using base64 encoder.

c2VsZWN0IGBzZGl0ZXN0X3ZlaGljbGVgLmB2aW5gIGZyb20gc2RpdGVzdF92ZWhpY2xlIHdoZXJlIGBzZGl0ZXN0X3ZlaGljbGVgLmB2aW5gPSA6ImBzZGl0ZXN0X3ZlaGljbGVgLmB2aW5gIiBhbmQgYHNkaXRlc3RfdmVoaWNsZWAuYGRlZmAgPSAnQk1XJw==

Query contains parameters and user may or may not provide alias for column name in select clause at runtime.

isDynamic = true

The query is created using schema and its properties.

isBusinessQuery = false

Create query request JSON:

{
  "description": "Test Query",
  "isDynamic": true,
  "isBusinessQuery": false,
  "name": "crashanalysis",
  "sqlStatement": "c2VsZWN0IGBzZGl0ZXN0X3ZlaGljbGVgLmB2aW5gIGZyb20gc2RpdGVzdF92ZWhpY2xlIHdoZXJlIGBzZGl0ZXN0X3ZlaGljbGVgLmB2aW5gPSA6ImBzZGl0ZXN0X3ZlaGljbGVgLmB2aW5gIiBhbmQgYHNkaXRlc3RfdmVoaWNsZWAuYGRlZmAgPSAnQk1XJw=="
}

The value of the parameter needs to be provided in execution job creation request.

Create execution job request JSON:

{
  "description": "Running query with sample alias AND parameters",
  "parameters": [
    {
      "paramName": "`sditest_vehicle`.`vin`",
      "paramValue": 123
    }
  ]
}

Select few attributes from a schema with dynamic parameters and user is willing to provide alias for a aggregate function at execution time¶

The query created by a user is as given below:

SELECT COUNT(sditest_vehicle.vin) FROM sditest_vehicle WHERE sditest_vehicle.vin = :"sditest_vehicle.vin" AND sditest_vehicle.def = 'BMW'

To add this query to create request, we need to encode it using base64 encoder.

c2VsZWN0IGNvdW50KHNkaXRlc3RfdmVoaWNsZS52aW4pIGZyb20gc2RpdGVzdF92ZWhpY2xlIHdoZXJlIHNkaXRlc3RfdmVoaWNsZS52aW4gPSA6InNkaXRlc3RfdmVoaWNsZS52aW4iICBhbmQgc2RpdGVzdF92ZWhpY2xlLmRlZiA9ICdCTVcn

Query contains parameters and user may provide alias for column name in select clause at runtime.

isDynamic = true

The query is created using schema and its properties

isBusinessQuery = false

Create query request JSON:

{
  "description": "Test Query",
  "isDynamic": true,
  "name": "crashanalysis",
  "isBusinessQuery": false,
  "sqlStatement": "c2VsZWN0IGNvdW50KHNkaXRlc3RfdmVoaWNsZS52aW4pIGZyb20gc2RpdGVzdF92ZWhpY2xlIHdoZXJlIHNkaXRlc3RfdmVoaWNsZS52aW4gPSA6InNkaXRlc3RfdmVoaWNsZS52aW4iICBhbmQgc2RpdGVzdF92ZWhpY2xlLmRlZiA9ICdCTVcn"
}

The value of the parameter and alias needs to be provided in execution job creation request.

Create execution job request JSON:

{
  "description": "Running query with sample alias and parameters",
  "parameters": [
    {
      "paramName": "sditest_vehicle.vin",
      "paramValue": "'abc'"
    }
  ],
  "aliases": [
    {
      "attributeName": "COUNT(sditest_vehicle.vin)",
      "aliasValue": "Vin"
    }
  ]
}

Select ifnull query¶

The query created by a user is as given below:

SELECT IFNULL(NULL, 500)

for ifnull queries, the spark requires an alias to be added in the query.

SELECT IFNULL(NULL, 500) AS value

To add this query to create request, encode it using base64 encoder.

U0VMRUNUIElGTlVMTChOVUxMLCA1MDApIGFzIHZhbHVl

The query does not contain parameters. Alias for a column name is provided at the time of query creation only and user is not willing to provide any alias at runtime.

isDynamic = false

The query is created using schema and its properties.

isBusinessQuery = false

Create query request JSON:

{
  "description": "Test Query",
  "isDynamic": false,
  "name": "crashanalysis",
  "isBusinessQuery": false,
  "sqlStatement": "U0VMRUNUIElGTlVMTChOVUxMLCA1MDApIGFzIHZhbHVl"
}

Select query having alias¶

The query created by a user is as given below:

SELECT sditest_vehicle.vin AS VIN FROM sditest_vehicle WHERE sditest_vehicle.def = 'BMW'

To add this query to create request, we need to encode it using base64 encoder.

c2VsZWN0IGBzZGl0ZXN0X3ZlaGljbGVgLmB2aW5gIGFzIFZJTiBmcm9tIGBzZGl0ZXN0X3ZlaGljbGVgIHdoZXJlIGBzZGl0ZXN0X3ZlaGljbGVgLmBkZWZgID0gJ0JNVyc=

Query does not contain parameters and user may or may not provide alias for column name in select clause at runtime.

isDynamic = true

The query is created using schema and its properties.

isBusinessQuery = false

Create query request JSON:

{
  "description": "Test Query",
  "isDynamic": true,
  "name": "crashanalysis",
  "isBusinessQuery": false,
  "sqlStatement": "c2VsZWN0IGBzZGl0ZXN0X3ZlaGljbGVgLmB2aW5gIGFzIFZJTiBmcm9tIGBzZGl0ZXN0X3ZlaGljbGVgIHdoZXJlIGBzZGl0ZXN0X3ZlaGljbGVgLmBkZWZgID0gJ0JNVyc="
}

The value of the alias needs to be provided in execution job creation request.

Create execution job request JSON:

{
  "description": "Running query with sample alias and parameters",
  "aliases": [
    {
      "attributeName": "VIN",
      "aliasValue": "Vin"
    }
  ]
}

Business Query with mapping between one class property to many schema properties¶

• Mapping details

  • classProperty

    Class Name	Class Property
    Customers	CustomerID

  • schemaProperties

    Schema Name	Schema Property
    retailtest_customers	customerid
    retailtest_orders	customerid

• Data in tables

  • retailtest_customers

    customerid
    1
    2
    3

  • retailtest_orders

    customerid
    3
    4
    5

The query created by a user is as given below:

SELECT Customers.CustomerID FROM Customers

To add this query to create request, we need to encode it using base64 encoder.

U0VMRUNUIEN1c3RvbWVycy5DdXN0b21lcklEIEZST00gQ3VzdG9tZXJz

Query does not contain parameters and user does not want to use alias at runtime.

isDynamic = false

The query is created using class/es and its properties

isBusinessQuery = true

Create query request JSON:

{
  "description": "Test Query",
  "isDynamic": false,
  "name": "crashanalysis",
  "isBusinessQuery": true,
  "sqlStatement": "U0VMRUNUIEN1c3RvbWVycy5DdXN0b21lcklEIEZST00gQ3VzdG9tZXJz"
}

Execution Result:

Business Query with mapping between one class property to one schema property¶

• Mapping details

  • classProperty

    Class Name	Class Property
    Customers	CustomerID

  • schemaProperties

    Schema Name	Schema Property
    retailtest_customers	customerid

• Data in tables

  • retailtest_customers

    customerid
    1
    2
    3

The query created by a user is as given below:

SELECT Customers.CustomerID FROM Customers

To add this query to create request, encode it using base64 encoder.

U0VMRUNUIEN1c3RvbWVycy5DdXN0b21lcklEIEZST00gQ3VzdG9tZXJz

Query does not contain parameters and user does not want to use alias at runtime.

isDynamic = false

The query is created using class/es and its properties

isBusinessQuery = true

Create query request JSON:

{
  "description": "Test Query",
  "isDynamic": false,
  "isBusinessQuery": true,
  "name": "crashanalysis",
  "sqlStatement": "U0VMRUNUIEN1c3RvbWVycy5DdXN0b21lcklEIEZST00gQ3VzdG9tZXJz"
}

Execution Result:

Business Query with mapping between many class properties to one schema property¶

• Mapping details

  • classProperty

    Class Name	Class Property
    Customers	CustomerName
    Customers	ContactName

  • schemaProperties

    Schema Name	Schema Property
    retailtest_customers	customername

• Data in tables

  • retailtest_customers

    customername
    customerA
    customerA
    customerC

The query created by a user is as given below:

SELECT Customers.CustomerName, Customers.ContactName FROM Customers

To add this query to create request, encode it using base64 encoder.

U0VMRUNUIEN1c3RvbWVycy5DdXN0b21lck5hbWUsIEN1c3RvbWVycy5Db250YWN0TmFtZSBGUk9NIEN1c3RvbWVycw==

Query does not contain parameters and user does not want to use alias at runtime.

isDynamic = false

The query is created using class/es and its properties

isBusinessQuery = true

Create query request JSON:

{
  "description": "Test Query",
  "isDynamic": false,
  "name": "crashanalysis",
  "isBusinessQuery": true,
  "sqlStatement": "U0VMRUNUIEN1c3RvbWVycy5DdXN0b21lck5hbWUsIEN1c3RvbWVycy5Db250YWN0TmFtZSBGUk9NIEN1c3RvbWVycw=="
}

Execution Result:

Business Query containing having, order by and group by clause¶

The query created by a user is as given below:

SELECT
  COUNT(Customers.CustomerID),
  Customers.CustomerID,
  Customers.Country
FROM
  Customers
GROUP BY
  Customers.Country,
  Customers.CustomerID
HAVING
  COUNT(Customers.CustomerID) > 5
ORDER BY
  COUNT(Customers.CustomerID) DESC

In above query, as the ORDER BY clause is used with COUNT(Customers.CustomerID). So, Customers.CustomerID is required to be added in SELECT clause.

Also, HAVING clause is used with COUNT(Customers.CustomerID). So, COUNT(Customers.CustomerID) is required to be added to select clause. Please refer limitations section for more details.

To add this query to create request, encode it using base64 encoder.

U0VMRUNUIGNvdW50KEN1c3RvbWVycy5DdXN0b21lcklEKSwgQ3VzdG9tZXJzLkN1c3RvbWVySUQsIEN1c3RvbWVycy5Db3VudHJ5IEZST00gQ3VzdG9tZXJzIEdST1VQIEJZIEN1c3RvbWVycy5Db3VudHJ5LCBDdXN0b21lcnMuQ3VzdG9tZXJJRCBIQVZJTkcgY291bnQoQ3VzdG9tZXJzLkN1c3RvbWVySUQpID4gNSBPUkRFUiBCWSBjb3VudChDdXN0b21lcnMuQ3VzdG9tZXJJRCkgREVTQw==

Query does not contain parameters and user does not want to use alias at runtime.

isDynamic = false

The query is created using class/es and its properties

isBusinessQuery = true

Create query request JSON:

{
  "description": "Test Query",
  "isDynamic": false,
  "name": "crashanalysis",
  "isBusinessQuery": true,
  "sqlStatement": "U0VMRUNUIGNvdW50KEN1c3RvbWVycy5DdXN0b21lcklEKSwgQ3VzdG9tZXJzLkN1c3RvbWVySUQsIEN1c3RvbWVycy5Db3VudHJ5IEZST00gQ3VzdG9tZXJzIEdST1VQIEJZIEN1c3RvbWVycy5Db3VudHJ5LCBDdXN0b21lcnMuQ3VzdG9tZXJJRCBIQVZJTkcgY291bnQoQ3VzdG9tZXJzLkN1c3RvbWVySUQpID4gNSBPUkRFUiBCWSBjb3VudChDdXN0b21lcnMuQ3VzdG9tZXJJRCkgREVTQw=="
}

Select few attributes from a Class with dynamic parameters¶

The query created by a user is as given below:

SELECT
  COUNT(Customers.CustomerID),
  Customers.CustomerID,
  Customers.Country
FROM
  Customers
WHERE
  Customers.Country = :"Customers.Country"

To add this query to create request, encode it using base64 encoder.

U0VMRUNUIGNvdW50KEN1c3RvbWVycy5DdXN0b21lcklEKSwgQ3VzdG9tZXJzLkN1c3RvbWVySUQsIEN1c3RvbWVycy5Db3VudHJ5IEZST00gQ3VzdG9tZXJzIFdIRVJFIEN1c3RvbWVycy5Db3VudHJ5ID0gOiJDdXN0b21lcnMuQ291bnRyeSI=

Query contains parameters and user may or may not provide alias for column name in select clause at runtime.

isDynamic = true

The query is created using class and its properties

isBusinessQuery = true

Create query request JSON:

{
  "description": "Test Query",
  "isDynamic": true,
  "isBusinessQuery": true,
  "name": "crashanalysis",
  "sqlStatement": "U0VMRUNUIGNvdW50KEN1c3RvbWVycy5DdXN0b21lcklEKSwgQ3VzdG9tZXJzLkN1c3RvbWVySUQsIEN1c3RvbWVycy5Db3VudHJ5IEZST00gQ3VzdG9tZXJzIFdIRVJFIEN1c3RvbWVycy5Db3VudHJ5ID0gOiJDdXN0b21lcnMuQ291bnRyeSI="
}

The value of the parameter needs to be provided in execution job creation request.

Create execution job request JSON:

{
  "description": "Running query with sample alias and parameters",
  "parameters": [
    {
      "paramName": "Customers.Country",
      "paramValue": "'India'"
    }
  ],
  "aliases": [
    {
      "attributeName": "COUNT(Customers.CustomerID)",
      "aliasValue": "Customer ID Count"
    },
    {
      "attributeName": "Customers.Country",
      "aliasValue": "Country"
    }
  ]
}

Example to get latest results¶

The Data Contextualization schema adds sdi_last_modified_date for each data ingest jobs. This can be used to query latest data by performing the following:

1. Create an attribute, for example, LastModifiedDate in semantic model and mapped to sdi_last_modified_date.

2. Use this query to filter latest results based on the last modified date.

   SELECT
     Customers.CustomerID,
     Customers.Country
   FROM
     Customers
   WHERE
     Customers.LastModifiedDate = (
       SELECT MAX(Customers.LastModifiedDate) FROM Customers
     )
   

   To add this query to create request, we need to encode it using base64 encoder.

   U0VMRUNUIEN1c3RvbWVycy5DdXN0b21lcklELCBDdXN0b21lcnMuQ291bnRyeSBGUk9NIEN1c3RvbWVycyAgV0hFUkUgQ3VzdG9tZXJzLkxhc3RNb2RpZmllZERhdGUgPSAoU0VMRUNUIE1BWChDdXN0b21lcnMuTGFzdE1vZGlmaWVkRGF0ZSkgRlJPTSBDdXN0b21lcnMp
   

Example to avoid duplicate results¶

The Data Contextualization schema on read does not have constraints added. So, query result may contain duplicate data. To remove duplication use DISTINCT :

SELECT DISTINCT Customers.CustomerID, Customers.Country FROM Customers

To add this query to create request, we need to encode it using base64 encoder.

ICAgIFNFTEVDVCBESVNUSU5DVCBDdXN0b21lcnMuQ3VzdG9tZXJJRCwgQ3VzdG9tZXJzLkNvdW50cnkgRlJPTSBDdXN0b21lcnMgIA==

Related Links¶

• How to create executionJobs for dynamic query

Except where otherwise noted, content on this site is licensed under the Development License Agreement.