Kinetica SQL

https://docs.kinetica.com/7.2/sql/
Admin
Kinetica SQL provides a comprehensive interface for analytics, data manipulation, and definition,...
Tokens:271,086
Snippets:2,126
Trust Score:10
Update:10 months ago
Show doc for...
Tokens:
Raw
### Path Query Example

Source: https://docs.kinetica.com/7.2/sql/graph

Example demonstrating how to query for paths between nodes, including relationships and conditions.

```APIDOC
## Path Query Example

### Description
This query finds paths in the 'employee_graph' where two employees (e1 and e2) work with each other, but have different direct managers (m1 and m2). The results include the managers and the employees involved in the 'WORKS WITH' relationship, ordered by their node identifiers.

### Method
GRAPH

### Endpoint
N/A (This is a query statement)

### Query
```
GRAPH employee_graph
MATCH (m1)-[r1:MANAGES]->(e1)-[ww:"WORKS WITH"]->(e2)<-[r2:MANAGES]-(m2)
WHERE m1 != m2
RETURN m1, e1, e2, m2
ORDER BY m1.node, e1.node, e2.node
```
```

--------------------------------

### Install Python Packages in UDF Environment

Source: https://docs.kinetica.com/7.2/sql/udf

Installs specified Python packages, optionally with version numbers, into a named UDF environment. This command requires the environment name and a list of packages to install.

```sql
ALTER FUNCTION ENVIRONMENT <environment name>
INSTALL PYTHON PACKAGE '<package name>[==<package version>] [...]>'
```

```sql
ALTER FUNCTION ENVIRONMENT udfe_st
INSTALL PACKAGE 'urllib3==1.26.18 sentence-transformers'
```

--------------------------------

### Kinetica SHOW FUNCTION Example

Source: https://docs.kinetica.com/7.2/sql/udf

An example demonstrating how to view the configuration (DDL) of the Kinetica UDF `UDF_SOS_PY_PROC`. This command is useful for inspecting function definitions and understanding their settings.

```SQL
SHOW FUNCTION UDF_SOS_PY_PROC
```

--------------------------------

### Kinetica SQL CREATE CONTEXT Example

Source: https://docs.kinetica.com/7.2/sql/-gpt/concepts

Provides a practical example of creating a context named 'nyctaxi_ctx'. This context includes definitions for the 'taxi_data' and 'nyct2020' tables, sample queries, and specific rules to guide SQL-GPT's query generation process, such as using spatial functions and date extraction.

```sql
CREATE OR REPLACE CONTEXT nyctaxi_ctx
(
    TABLE = taxi_data,
    COMMENT =
    ' 
        This table contains historical New York taxi trip data.
        Each row contains the trip starting (pickup) and ending
        (drop off) latitudes and longitudes, the total fare,
        and other details about the trip.
    ',
    COMMENTS =
    (
        vendor_id = 'The ID for the taxi company providing the ride.',
        pickup_datetime = 'The time at which a trip started.',
        dropoff_datetime = 'The time at which a trip ended',
        trip_distance = 'The total distance in kilometers for a particular trip'
        
    ),
    RULES =
    (
        'Do not use the surcharge column of the taxi_data table',
        'Payment types include cash, credit, dispute, no charge, and unknown'
    )
),
(
    TABLE = nyct2020,
    COMMENT = 'This table contains Neighborhood Tabulation Area (NTA) geoms and names for NYC neighborhoods',
    COMMENTS =
    (
        gid = 'The primary key that identifies each row in this table',
        geom = 'The WKT that provides the spatial outline for the neighborhood',
        NTAName = 'The name of the NTA neighborhood.'
    )
    RULES =
    (
        'Use the nyct2020 table to calculate aggregate statistics for neighborhoods',
        'Use NTAName instead of spatial coordinates to identify a neighborhood'
    )
),
(
    SAMPLES =
    (
        'How many trips did each taxi vendor''s cabs make?' =
        ' 
            SELECT vendor_id, COUNT(*)
            FROM taxi_data
            GROUP BY vendor_id
        '
    )
),
(
    RULES =
    (
        ' 
            Use the ST_CONTAINS() function to join the geom column of the nyct2020 table
            with the pickup_longitude and pickup_latitude columns of the taxi_data table
            or the dropoff_longitude and dropoff_latitude columns of the taxi_data table
        ',
        'Use functions like HOUR(expr) DAY(expr) etc to extract a date/time expression''s components'
    )
)
```

--------------------------------

### Kinetica Table Definition Clause Example

Source: https://docs.kinetica.com/7.2/sql/-gpt/concepts

An example demonstrating how to define a table named 'taxi_data' with a descriptive comment, comments for specific columns (vendor_id, pickup_datetime, dropoff_datetime, trip_distance), and two rules to guide SQL generation.

```sql
(
    TABLE = taxi_data,
    COMMENT =
    '
        This table contains historical New York taxi trip data.
        Each row contains the trip starting (pickup) and ending
        (drop off) latitudes and longitudes, the total fare,
        and other details about the trip.
    ',
    COMMENTS = 
    (
        vendor_id = 'The ID for the taxi company providing the ride.',
        pickup_datetime = 'The time at which a trip started.',
        dropoff_datetime = 'The time at which a trip ended',
        trip_distance = 'The total distance in kilometers for a particular trip'
        
    ),
    RULES = 
    (
        'Do not use the surcharge column of the taxi_data table',
        'Payment types include cash, credit, dispute, no charge, and unknown'
    )
)
```

--------------------------------

### Match Graph Table Function Example (SQL)

Source: https://docs.kinetica.com/7.2/sql/graph

This example demonstrates how to use the MATCH_GRAPH table function in SQL to find optimal routes. It requires a graph, sample points, and a solve method. The results are returned directly by the table function.

```sql
SELECT *
FROM TABLE
(
    MATCH_GRAPH
    (
        GRAPH => 'big_cities_graph',
        SAMPLE_POINTS => INPUT_TABLE
        (
            SELECT
                city1_location AS ORIGIN_WKTPOINT,
                city2_location AS DESTINATION_WKTPOINT,
                trip_time AS OD_TIME
            FROM big_cities_map
        ),
        SOLVE_METHOD => 'match_od_pairs'
    )
)
```

--------------------------------

### Create SQL Procedure Example - Kinetica SQL

Source: https://docs.kinetica.com/7.2/sql/procedure

An example of creating a basic SQL procedure that creates a temporary table and then selects from it.

```SQL
CREATE PROCEDURE example.sqlp
BEGIN
    CREATE OR REPLACE TABLE example.sqlp_table_tmp AS
    SELECT *
    FROM example.sqlp_table_ref;

    SELECT * 
    FROM example.sqlp_table_tmp;
END
```

--------------------------------

### GRANT SYSTEM READ Example

Source: https://docs.kinetica.com/7.2/sql/security

An example showing how to grant read access on all tables to a role named 'auditor'.

```sql
GRANT SYSTEM READ TO auditor
```

--------------------------------

### Variable Hop Path Query Example

Source: https://docs.kinetica.com/7.2/sql/graph

Example of querying for paths with a variable number of hops (edges) between nodes.

```APIDOC
## Variable Hop Path Query Example

### Description
This query finds all managers ('m') connected to employee with node ID 14 ('e') within 1 to 10 hops (management levels) in the 'employee_graph'. The results are ordered by the manager's node identifier.

### Method
GRAPH

### Endpoint
N/A (This is a query statement)

### Query
```
GRAPH employee_graph
MATCH (m)-[r1:MANAGES]->{1,10}(e {node: 14})
RETURN m, e
ORDER BY m.node
```
```

--------------------------------

### Match Graph Execute Function Example (SQL)

Source: https://docs.kinetica.com/7.2/sql/graph

This example shows how to use the MATCH_GRAPH function with the EXECUTE FUNCTION syntax in SQL. This method requires a SOLUTION_TABLE parameter to store the match results. It configures the graph, sample points, and solve method.

```sql
EXECUTE FUNCTION MATCH_GRAPH
(
    GRAPH => 'big_cities_graph',
    SAMPLE_POINTS => INPUT_TABLE
    (
        SELECT
            city1_location AS ORIGIN_WKTPOINT,
            city2_location AS DESTINATION_WKTPOINT,
            trip_time AS OD_TIME
        FROM big_cities_map
    ),
    SOLVE_METHOD => 'match_od_pairs',
    SOLUTION_TABLE => 'big_cities_graph_matched'
)
```

--------------------------------

### SQL CREATE TABLE Example

Source: https://docs.kinetica.com/7.2/sql/ddl

An example demonstrating the creation of a table named 'various_types' within the 'example' schema. This example showcases various column definitions, including different data types, NOT NULL constraints, and comments.

```sql
CREATE TABLE example.various_types
(
    i   INTEGER NOT NULL                                  COMMENT 'non-nullable integer, part of primary key (defined at end)',
    bi  BIGINT NOT NULL                                   COMMENT 'long, part of primary key, shard key, foreign key source (defined at end)',
    b   BOOLEAN                                           COMMENT '0s and 1s only',
    ub  UNSIGNED BIGINT                                   COMMENT 'native unsigned long',
    r   REAL                                              COMMENT 'native float',
    d   DOUBLE                                            COMMENT 'native double',
    s   VARCHAR(TEXT_SEARCH)                              COMMENT 'string, searchable, only limited in size by system-configured value',
    c   VARCHAR(30, DICT)                                 COMMENT 'char32 using dictionary-encoding of values',
    p   VARCHAR(256, TEXT_SEARCH)                         COMMENT 'char256, searchable',
    ip  IPV4                                              COMMENT 'IP address',
    ui  UUID(INIT_WITH_UUID)                              COMMENT 'UUID',
    ts  TIMESTAMP                                         COMMENT 'timestamp'
```

--------------------------------

### GRANT SYSTEM ADMIN Example

Source: https://docs.kinetica.com/7.2/sql/security

An example demonstrating how to grant system administrator permission to a user named 'jsmith'.

```sql
GRANT SYSTEM ADMIN TO jsmith
```

--------------------------------

### Graph Solvers - Table Function Example

Source: https://docs.kinetica.com/7.2/sql/graph

This example demonstrates how to use the SOLVE_GRAPH table function to find the shortest path in a graph. Results are returned directly by the function.

```APIDOC
## SOLVE_GRAPH (Table Function)

### Description
Executes a graph solving operation and returns the results directly as a table.

### Method
SELECT

### Endpoint
N/A (Table Function)

### Parameters
#### Query Parameters
None

#### Request Body
None

### Request Example
```sql
SELECT * FROM TABLE
(
    SOLVE_GRAPH
    (
        GRAPH => 'sample_graph',
        SOLVER_TYPE => 'SHORTEST_PATH',
        SOURCE_NODES => INPUT_TABLE(SELECT ST_GEOMFROMTEXT('POINT(0 0)') AS WKTPOINT),
        DESTINATION_NODES => INPUT_TABLE(SELECT ST_GEOMFROMTEXT('POINT(-5 -5)') AS WKTPOINT),
        OPTIONS => KV_PAIRS(output_edge_path = 'true')
    )
)
```

### Response
#### Success Response (200)
- **(Table)**: Contains the results of the graph solving operation.

#### Response Example
(Table structure depends on the SOLVER_TYPE and OPTIONS)
```

--------------------------------

### Create User SQL Examples

Source: https://docs.kinetica.com/7.2/sql/security

Provides examples of SQL statements for creating internal users with passwords, and assigning resource groups. Also shows how to create users linked to external LDAP authentication.

```sql
CREATE USER jsmith IDENTIFIED BY 'secret'
CREATE USER jsmith WITH PASSWORD 'secret'
```

```sql
CREATE USER jsmith
IDENTIFIED BY 'secret'
WITH RESOURCE GROUP memory_over_execution
```

```sql
CREATE USER "@jsmith"
```

--------------------------------

### SQL-GPT: Generate SQL Example

Source: https://docs.kinetica.com/7.2/sql/-gpt/concepts

Provides an example of how to use the GENERATE SQL syntax to generate a SQL query for a specific question about stock trades, using a pre-defined context named 'stock_trades_context'.

```sql
GENERATE SQL FOR
' For each trade of the Apple stock find a corresponding
 quote that is less than 5 seconds after the trade occurred?'
WITH OPTIONS (context_name = 'stock_trades_context')
```

--------------------------------

### Graph Solvers - EXECUTE FUNCTION Example

Source: https://docs.kinetica.com/7.2/sql/graph

This example shows how to use the SOLVE_GRAPH function with the EXECUTE FUNCTION syntax. Results are stored in a specified SOLUTION_TABLE.

```APIDOC
## SOLVE_GRAPH (EXECUTE FUNCTION)

### Description
Executes a graph solving operation and stores the results in a specified table.

### Method
EXECUTE FUNCTION

### Endpoint
N/A (Function Execution)

### Parameters
#### Query Parameters
None

#### Request Body
None

### Request Example
```sql
EXECUTE FUNCTION SOLVE_GRAPH
(
    GRAPH => 'sample_graph',
    SOLVER_TYPE => 'SHORTEST_PATH',
    SOURCE_NODES => INPUT_TABLE(SELECT ST_GEOMFROMTEXT('POINT(0 0)') AS WKTPOINT),
    DESTINATION_NODES => INPUT_TABLE(SELECT ST_GEOMFROMTEXT('POINT(-5 -5)') AS WKTPOINT),
    SOLUTION_TABLE => 'sample_graph_solved_shortest_path',
    OPTIONS => KV_PAIRS(output_edge_path = 'true')
)
```

### Response
#### Success Response (200)
- **(Status)**: Indicates successful execution.

#### Response Example
(Typically returns a success status code or message. Actual results are in the SOLUTION_TABLE.)
```

--------------------------------

### GRANT USER ADMIN Example

Source: https://docs.kinetica.com/7.2/sql/security

An example demonstrating how to grant user administrator permission to a user named 'jsmith'.

```sql
GRANT USER ADMIN TO jsmith
```

--------------------------------

### EXECUTE FUNCTION Example

Source: https://docs.kinetica.com/7.2/sql/graph

This example shows how to use the `EXECUTE FUNCTION` syntax for graph queries. Results are stored in an `ADJACENCY_TABLE`.

```APIDOC
## EXECUTE FUNCTION Example

### Description
This example shows how to use the `EXECUTE FUNCTION` syntax for graph queries. Results are stored in an `ADJACENCY_TABLE`.

### Method
EXECUTE FUNCTION

### Endpoint
N/A (SQL Query)

### Parameters
#### Query Parameters
- **GRAPH** (string) - Required - The name of the graph to query.
- **QUERIES** (INPUT_TABLE) - Required - A table specifying the starting nodes or edges for the query.
- **RINGS** (integer) - Required - The number of hops or traversal depth.
- **ADJACENCY_TABLE** (string) - Required - The name of the table where the query results will be stored.
- **OPTIONS** (KV_PAIRS) - Optional - Key-value pairs for additional options like `output_wkt_path`.

### Request Example
```sql
EXECUTE FUNCTION QUERY_GRAPH
(
    GRAPH => 'sample_graph',
    QUERIES => INPUT_TABLE(SELECT ST_GEOMFROMTEXT('POINT(5 5)') AS NODE_WKTPOINT),
    RINGS => 2,
    ADJACENCY_TABLE => 'sample_graph_node_query',
    OPTIONS => KV_PAIRS(output_wkt_path = 'true')
)
```

### Response
#### Success Response (200)
- **Execution Status** (string) - Indicates the success or failure of the function execution.

#### Response Example
(Example response will vary based on execution status)
```

--------------------------------

### UPDATE Example (SQL)

Source: https://docs.kinetica.com/7.2/sql/dml

A practical example demonstrating how to update specific columns for a record identified by its primary key. This example increases salary by 10% and changes the manager ID.

```SQL
UPDATE example.employee
SET
    sal = sal * 1.10,
    manager_id = 3
WHERE id = 5
```

--------------------------------

### Manual List Partitioning Example by Year in SQL

Source: https://docs.kinetica.com/7.2/sql/ddl

Provides an SQL example for creating a table manually list-partitioned by year. This example partitions orders into specific year ranges, with a default partition for any years not explicitly defined. It demonstrates partitioning on a single column.

```sql
CREATE TABLE example.customer_order_manual_list_partition_by_year
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP NOT NULL
)
PARTITION BY LIST (YEAR(purchase_ts))
PARTITIONS
(
    order_2014_2016 VALUES (2014, 2015, 2016),
    order_2017      VALUES (2017),
    order_2018      VALUES (2018),
    order_2019      VALUES (2019)
)
```

--------------------------------

### Kinetica Sample Definition Clause Example

Source: https://docs.kinetica.com/7.2/sql/-gpt/concepts

An example of a sample definition clause with one question-answer pair. It shows how to ask a natural language question about taxi trips and provide the corresponding SQL query to count trips per vendor.

```sql
(
    SAMPLES = (
        'How many trips did each taxi vendor''s cabs make?' =
        '
            SELECT vendor_id, COUNT(*)
            FROM taxi_data
            GROUP BY vendor_id
        '
    )
)
```

--------------------------------

### Table Function (Edges) Example

Source: https://docs.kinetica.com/7.2/sql/graph

This example illustrates querying graph edges using the table function syntax. Results are returned directly.

```APIDOC
## Table Function (Edges) Example

### Description
This example illustrates querying graph edges using the table function syntax. Results are returned directly.

### Method
SELECT

### Endpoint
N/A (SQL Query)

### Parameters
#### Query Parameters
- **GRAPH** (string) - Required - The name of the graph to query.
- **QUERIES** (INPUT_TABLE) - Required - A table specifying the starting nodes or edges for the query.
- **RINGS** (integer) - Required - The number of hops or traversal depth.
- **OPTIONS** (KV_PAIRS) - Optional - Key-value pairs for additional options like `output_wkt_path`.

### Request Example
```sql
SELECT * FROM TABLE
(
    QUERY_GRAPH
    (
        GRAPH => 'sample_graph',
        QUERIES => INPUT_TABLE(SELECT ST_GEOMFROMTEXT('POINT(0 0)') AS NODE_WKTPOINT),
        RINGS => 1,
        OPTIONS => KV_PAIRS(output_wkt_path = 'true')
    )
)
```

### Response
#### Success Response (200)
- **Output Columns** (various) - Depending on the query and options, this will include graph data such as node/edge properties, WKT paths, etc.

#### Response Example
(Example response will vary based on graph data and query results)
```

--------------------------------

### Table Function (Nodes) Example

Source: https://docs.kinetica.com/7.2/sql/graph

This example demonstrates how to use the table function syntax to query graph nodes. The results are returned directly.

```APIDOC
## Table Function (Nodes) Example

### Description
This example demonstrates how to use the table function syntax to query graph nodes. The results are returned directly.

### Method
SELECT

### Endpoint
N/A (SQL Query)

### Parameters
#### Query Parameters
- **GRAPH** (string) - Required - The name of the graph to query.
- **QUERIES** (INPUT_TABLE) - Required - A table specifying the starting nodes or edges for the query.
- **RINGS** (integer) - Required - The number of hops or traversal depth.
- **OPTIONS** (KV_PAIRS) - Optional - Key-value pairs for additional options like `output_wkt_path` and `result_table_index`.

### Request Example
```sql
SELECT * FROM TABLE
(
    QUERY_GRAPH
    (
        GRAPH => 'sample_graph',
        QUERIES => INPUT_TABLE(SELECT ST_GEOMFROMTEXT('POINT(0 0)') AS NODE_WKTPOINT),
        RINGS => 1,
        OPTIONS => KV_PAIRS(output_wkt_path = 'true', result_table_index = '2')
    )
)
```

### Response
#### Success Response (200)
- **Output Columns** (various) - Depending on the query and options, this will include graph data such as node/edge properties, WKT paths, etc.

#### Response Example
(Example response will vary based on graph data and query results)
```

--------------------------------

### EXECUTE_AI_QUERY with RAG Example

Source: https://docs.kinetica.com/7.2/sql/-gpt/concepts

Example of executing an AI query using the EXECUTE_AI_QUERY function with RAG enabled. It demonstrates how to pass a question and RAG options to the function.

```sql
SELECT *
FROM TABLE
(
    EXECUTE_AI_QUERY
    (
        QUESTION => 'What was the busiest airport, including taxis and flights, during January and February of 2023?',
        OPTIONS => KV_PAIRS (context_names = 'nyctaxi_ctx, flights_ctx', use_rag = 'true')
    )
)
```

--------------------------------

### CREATE FUNCTION Example for a Distributed Python UDF

Source: https://docs.kinetica.com/7.2/sql/udf

This example demonstrates how to create a distributed Python User-Defined Function (UDF) named 'udf_st'. It specifies the execution mode, the command to run ('python'), the arguments for the command, the file paths, and an environment option.

```SQL
CREATE FUNCTION udf_st
MODE = 'distributed'
RUN_COMMAND = 'python'
RUN_COMMAND_ARGS = 'udf/udf_st.py'
FILE PATHS 'kifs://udf/udf_st.py'
WITH OPTIONS (SET_ENVIRONMENT = 'udfe_st')
```

--------------------------------

### Cypher Node Property Matcher Example

Source: https://docs.kinetica.com/7.2/sql/graph

Illustrates the Node Property Matcher in Cypher, showing how to specify multiple properties that a node must possess. This example requires the node to have 'section' equal to 12 and 'last_name' equal to 'Smith'.

```cypher
{section: 12, last_name: 'Smith'}
```

--------------------------------

### GENERATE SQL with RAG Example

Source: https://docs.kinetica.com/7.2/sql/-gpt/concepts

Example of generating SQL with RAG enabled. This query asks a question about busiest airports and uses specified contexts with RAG activated.

```sql
GENERATE SQL FOR
    'What was the busiest airport, including taxis and flights, during January and February of 2023?'
WITH OPTIONS (context_names = 'nyctaxi_ctx, flights_ctx', use_rag = 'true')
```

--------------------------------

### Kinetica Execute Table Function (UDTF) Example

Source: https://docs.kinetica.com/7.2/sql/udf

Illustrates the execution of a Kinetica UDTF named `UDTF_ST`. The example shows how to use the UDTF within a `SELECT` statement, providing input tables and parameters, and then joining its output with other data. This is useful for row-generating functions.

```SQL
SELECT TOP 5 score, sentence
FROM
    example.udf_st_in i,
    TABLE
    (
        UDTF_ST
        (
            INPUT_TABLE_NAMES => INPUT_TABLE(example.udf_st_in),
            PARAMS => KV_PAIRS(sentence = 'I live in a neighborhood in a small town.')
        )
    ) o
WHERE i.id = o.id
ORDER BY score DESC
```

--------------------------------

### GENERATE TOKENS with RAG Example

Source: https://docs.kinetica.com/7.2/sql/-gpt/concepts

Example of generating tokens using the GENERATE TOKENS function with RAG enabled. It specifies context names and sets 'use_rag' to 'true' to leverage RAG capabilities.

```sql
GENERATE TOKENS
WITH OPTIONS (context_names = 'nyctaxi_ctx, flights_ctx', use_rag = 'true')
```

--------------------------------

### Create Scheduled SQL Procedure Example - Kinetica SQL

Source: https://docs.kinetica.com/7.2/sql/procedure

An example of creating a SQL procedure that is scheduled to execute every 7 days, starting on a specific date.

```SQL
CREATE PROCEDURE example.sqlp_weekly
BEGIN
    CREATE OR REPLACE TABLE example.sqlp_table_tmp AS
    SELECT *
    FROM example.sqlp_table_ref;

    SELECT * 
    FROM example.sqlp_table_tmp;
END 
EXECUTE FOR EVERY 7 DAYS
STARTING AT '2019-01-01 00:00:00'
```

--------------------------------

### Create Graph with Nodes, Edges, and Weights from Input Tables

Source: https://docs.kinetica.com/7.2/sql/graph

This example demonstrates creating a graph with nodes, edges, and weights. Nodes are defined by WKTPoint, edges by connecting WKTPoints, and weights by trip time from input tables. It configures output graph table.

```sql
CREATE GRAPH big_cities_graph
(
    NODES => INPUT_TABLE
    (
        SELECT
            city_location AS WKTPOINT
        FROM big_cities
    ),
    EDGES => INPUT_TABLE
    (
        SELECT
            city1_location AS NODE1_WKTPOINT,
            city2_location AS NODE2_WKTPOINT
        FROM big_cities_map
    ),
    WEIGHTS => INPUT_TABLE
    (
        SELECT
            (REMOVE_NULLABLE(ST_MAKELINE(city1_location, city2_location))) AS EDGE_WKTLINE,
            trip_time AS VALUESPECIFIED
        FROM big_cities_map
    ),
    OPTIONS => KV_PAIRS(recreate = 'true', graph_table = 'big_cities_graph_table')
)
```

--------------------------------

### GRANT Read on Schema to Role Example

Source: https://docs.kinetica.com/7.2/sql/security

An example of granting SELECT privilege on all current and future tables and views within the 'app_schema' schema to the 'app_user' role.

```sql
GRANT SELECT ON app_schema TO app_user
```

--------------------------------

### Cypher MATCH Expression Example

Source: https://docs.kinetica.com/7.2/sql/graph

An example of a Cypher MATCH expression used in Kinetica GraphQL queries. This specific example matches a starting node with 'User' and 'Admin' labels and specific properties, connected to an ending node with the 'User' label.

```cypher
(js:User:Admin {first_name: 'Joe', last_name: 'Smith'})-[]->(:User)
```

--------------------------------

### Create Graph with Edges and Weights from Input Table

Source: https://docs.kinetica.com/7.2/sql/graph

This example shows how to create a graph by defining edges from a WKTLine column and weights from a 'cost' column in an input table. It specifies the input table and output graph table name.

```sql
CREATE GRAPH sample_graph
(
    EDGES => INPUT_TABLE
    (
        SELECT
            id AS ID,
            wktline AS WKTLINE
        FROM sample_map
    ),
    WEIGHTS => INPUT_TABLE
    (
        SELECT
            id AS EDGE_ID,
            cost AS VALUESPECIFIED
        FROM sample_map
    ),
    OPTIONS => KV_PAIRS(recreate = 'true', graph_table = 'sample_graph_table')
)
```

--------------------------------

### Kinetica SQL Recursive Query Example: Subordinates

Source: https://docs.kinetica.com/7.2/sql/query

An example of a recursive query in Kinetica SQL to find all subordinates under a specific manager. It starts with the manager and recursively joins to find employees whose manager is already in the result set.

```sql
WITH RECURSIVE subordinates AS
(
    SELECT 
        id, 
        manager_id, 
        first_name,
        last_name
    FROM 
        example.employee
    WHERE 
        id = 6
    UNION ALL
    SELECT 
        e.id, 
        e.manager_id, 
        e.first_name,
        e.last_name
    FROM 
        example.employee e 
        INNER JOIN subordinates s ON s.id = e.manager_id
) 
SELECT * 
FROM subordinates
ORDER BY manager_id, id
```

--------------------------------

### LOAD INTO (Kafka) Example

Source: https://docs.kinetica.com/7.2/sql/load

Subscribes to a Kafka data feed and streams data continuously into a target table. Specifies the Kafka data source and enables subscription.

```SQL
LOAD DATA INTO example.orders
FORMAT JSON
WITH OPTIONS (DATA SOURCE = 'kafka_ds', SUBSCRIBE = TRUE)
```

--------------------------------

### Remote Query Example: Employees and Managers

Source: https://docs.kinetica.com/7.2/sql/query

An example demonstrating how to query employee and manager information from a remote system. This showcases fetching data and performing joins within the remote query.

```sql
SELECT * FROM TABLE
(
    EXECUTE_REMOTE_QUERY
    (
        QUERY => '
            SELECT
                e.last_name || "," || e.first_name AS "Employee_Name",
                m.last_name || "," || m.first_name AS "Manager_Name"
            FROM
                example.employee e
                LEFT JOIN example.employee m ON e.manager_id = m.id
            WHERE
                e.dept_id IN (1, 2, 3)
            ORDER BY
                m.id ASC NULLS FIRST,
                e.hire_date
        ',
        OPTIONS => KV_PAIRS(datasource_name = 'example.jdbc_ds')
    )
)
```

--------------------------------

### SQL ILIKE: Case-Insensitive Prefix Match

Source: https://docs.kinetica.com/7.2/sql/query

Example showcasing the `ILIKE` operator for a case-insensitive match. It finds records where the `first_name` does not start with 'brook' (case-insensitive), using the '%' wildcard to match any subsequent characters.

```SQL
SELECT *
FROM example.employee
WHERE first_name NOT ILIKE 'brook%'
```

--------------------------------

### Set System Properties Permanently (SQL)

Source: https://docs.kinetica.com/7.2/sql/system_properties

This example demonstrates how to permanently alter system properties, such as request timeout and maximum get records size, by modifying the startup configuration. Ensure correct syntax for property names and values.

```SQL
ALTER SYSTEM SET PROPERTIES (
    'request_timeout' = '25',
    'max_get_records_size' = '25000'
)
```

--------------------------------

### SQL LIKE: Case-Sensitive Exact Length Match

Source: https://docs.kinetica.com/7.2/sql/query

Example demonstrating the `LIKE` operator for a case-sensitive match. It searches for records where the `last_name` starts with 'C', is followed by exactly three characters, and ends with 'a'. This highlights the use of '_' for single character matching.

```SQL
SELECT *
FROM example.employee
WHERE last_name LIKE 'C___a'
```

--------------------------------

### CREATE STREAM Basic Example

Source: https://docs.kinetica.com/7.2/sql/ddl

A fundamental example demonstrating how to create a stream named 'kin_stream' on the 'order_stream' table. It filters orders costing more than $10,000 and directs the output to a data sink named 'example.kin_dsink'.

```sql
CREATE STREAM example.kin_stream ON example.order_stream
WHERE cost > 10000.00
WITH OPTIONS (DATASINK_NAME = 'example.kin_dsink')
```

--------------------------------

### SQL: Create Interval Partitioned Table by Year

Source: https://docs.kinetica.com/7.2/sql/ddl

This example demonstrates how to create a table partitioned by year. It defines partitions for each year starting from 2014, with subsequent year partitions added automatically as needed. Records before 2014 are placed in the default partition.

```sql
CREATE TABLE example.customer_order_interval_partition_by_year
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP
)
PARTITION BY INTERVAL (YEAR(purchase_ts))
PARTITIONS
(
    STARTING AT (2014) INTERVAL (1)
)
```

--------------------------------

### Create Graph with Labels from Constants

Source: https://docs.kinetica.com/7.2/sql/graph

This example illustrates creating a graph where labels and label keys are defined using constants directly within the SQL statement. It sets up node and edge labels like 'Gender', 'Interest', and 'Relation'.

```sql
CREATE OR REPLACE GRAPH people
(
    NODES => INPUT_TABLES
    (
        (SELECT 'Gender' AS LABEL_KEY, 'MALE:FEMALE' AS LABEL),
        (SELECT 'Interest' AS LABEL_KEY, 'golf:business:dance:chess' AS LABEL),

        (SELECT 'Jane' AS NAME, 'FEMALE:business' AS LABEL),
        (SELECT 'Bill' AS NAME, 'MALE:golf' AS LABEL),
        (SELECT 'Susan' AS NAME, 'FEMALE:dance' AS LABEL),
        (SELECT 'Alex' AS NAME, 'MALE:chess' AS LABEL),
        (SELECT 'Tom' AS NAME, 'MALE:chess' AS LABEL)
    ),
    EDGES => INPUT_TABLES
    (
        (SELECT 'Relation' AS LABEL_KEY, 'Friend:Family' AS LABEL),
        
        (SELECT 'Jane' AS NODE1_NAME, 'Bill' AS NODE2_NAME, 'Friend' AS LABEL),
        (SELECT 'Bill' AS NODE1_NAME, 'Alex' AS NODE2_NAME, 'Family' AS LABEL),
        (SELECT 'Bill' AS NODE1_NAME, 'Susan' AS NODE2_NAME, 'Friend' AS LABEL),
        (SELECT 'Susan' AS NODE1_NAME, 'Alex' AS NODE2_NAME, 'Friend' AS LABEL),
        (SELECT 'Alex' AS NODE1_NAME, 'Tom' AS NODE2_NAME, 'Friend' AS LABEL)    
    ),
    OPTIONS => KV_PAIRS(label_delimiter = ':', graph_table = 'people_graph_table')
)
```

--------------------------------

### SQL: Create Interval Partitioned Table by Day (INTERVAL function)

Source: https://docs.kinetica.com/7.2/sql/ddl

This example illustrates creating a daily interval-partitioned table using the INTERVAL function directly on the timestamp column. It defines partitions starting from '2014-01-01' with a 1-day interval, suitable for time-series data.

```sql
CREATE TABLE example.customer_order_interval_partition_by_day_interval
(
    id           INT NOT NULL,
    customer_id  INT NOT NULL,
    total_price  DECIMAL(10,2),
    purchase_ts  TIMESTAMP
)
PARTITION BY INTERVAL (purchase_ts)
PARTITIONS
(
    STARTING AT ('2014-01-01') INTERVAL (INTERVAL '1' DAY)
```

--------------------------------

### Kinetica Header Example for LOAD INTO

Source: https://docs.kinetica.com/7.2/sql/load

Demonstrates loading data from a CSV file with Kinetica headers. The target table is created if it doesn't exist, inferring schema from the header.

```SQL
LOAD DATA INTO example.product_create_on_load
FROM FILE PATHS 'kifs://data/products.kh.csv'
```

--------------------------------

### LOAD INTO: Create Table with Primary and Shard Keys

Source: https://docs.kinetica.com/7.2/sql/load

Demonstrates how to use the LOAD INTO command to create a target table with composite primary keys and a shard key if the table does not already exist. The data file is assumed to not have Kinetica headers.

```sql
LOAD INTO example.employee
FROM FILE PATHS 'kifs://data/employee.csv'
WITH OPTIONS
(
    PRIMARY KEY = (id, dept_id),
    SHARD KEY = (id)
)
```

--------------------------------

### Creating a UDF Directory and Uploading a UDF File

Source: https://docs.kinetica.com/7.2/sql/udf

Before creating a UDF/UDTF, you need to create a directory in KiFS and upload the function file. This example shows the SQL commands to create a directory named 'udf' and upload a Python file named 'udf_st.py' into it.

```SQL
CREATE DIRECTORY 'udf';
UPLOAD FILE 'udf_st.py'
INTO 'udf'
```

--------------------------------

### Drop Stream Example (SQL)

Source: https://docs.kinetica.com/7.2/sql/ddl

This is an example of how to drop a specific Kinetica stream named 'kin_stream' from the 'example' schema. This command will remove the stream and cease its operations.

```sql
DROP STREAM example.kin_stream
```

--------------------------------

### Query Managers by Department and Salary (Node Query)

Source: https://docs.kinetica.com/7.2/sql/graph

This example demonstrates a basic node query to find managers within a specific department who meet a certain salary threshold. It utilizes the MATCH and WHERE clauses to filter nodes based on properties.

```sql
GRAPH employee_graph
MATCH (a:MANAGER {"dept_id": 6})
WHERE salary > 150000
RETURN a
ORDER BY a.node
```

--------------------------------

### Cypher Edge Property Matcher Example

Source: https://docs.kinetica.com/7.2/sql/graph

An example of the Edge Property Matcher in Cypher, specifying that an edge must have certain properties with specific values. This example requires the edge to have 'manages' as true and 'years' as 3.

```cypher
{manages: true, years: 3}
```

--------------------------------

### LOAD INTO (Parquet) Example

Source: https://docs.kinetica.com/7.2/sql/load

Loads data from a Parquet file into a target table. The FORMAT PARQUET clause specifies the file format.

```SQL
LOAD DATA INTO example.employee_2000
FROM FILE PATHS 'kifs://data/employee.parquet'
FORMAT PARQUET
```

--------------------------------

### Query Managers in Management Chain (Variable Hop Path Query)

Source: https://docs.kinetica.com/7.2/sql/graph

This example demonstrates querying for managers within a specified number of hops (1 to 10 levels) from a target employee. It uses variable-length path matching to explore hierarchical relationships.

```sql
GRAPH employee_graph
MATCH (m)-[r1:MANAGES]->{1,10}(e {node: 14})
RETURN m, e
ORDER BY m.node
```

--------------------------------

### Describe Python Packages in UDF Environment

Source: https://docs.kinetica.com/7.2/sql/udf

Displays the list of installed Python packages (user-installed and default) for a given Python UDF/UDTF environment. It can list packages for a specific environment or all environments using '*'.

```sql
DESC[RIBE] FUNCTION ENVIRONMENT < <environment name> | * >
```

```sql
DESC FUNCTION ENVIRONMENT udfe_st
```

```sql
DESC FUNCTION ENVIRONMENT *
```

--------------------------------

### LOAD INTO: Create Table with Creation Options (Chunk Size, TTL)

Source: https://docs.kinetica.com/7.2/sql/load

Shows how to load data into a table, creating it if it doesn't exist, and specifying table creation options such as chunk size and time-to-live (TTL). The data file is assumed to not have Kinetica headers.

```sql
LOAD DATA INTO example.product_create_options
FROM FILE PATHS 'kifs://data/products.csv'
USING TABLE PROPERTIES (CHUNK SIZE = 1000000, TTL = 5)
```

--------------------------------

### Edge Query Example

Source: https://docs.kinetica.com/7.2/sql/graph

Example of a query to retrieve specific edges within the graph based on defined criteria.

```APIDOC
## Edge Query Example

### Description
This query finds all edges with the label 'MANAGES' originating from employee ID 2 in the 'employee_graph'. It filters for edges where the 'years' attribute is 3 or greater. The results are ordered by the source and destination node identifiers.

### Method
GRAPH

### Endpoint
N/A (This is a query statement)

### Query
```
GRAPH employee_graph
MATCH ()-[b:MANAGES {node1: 2}]->()
WHERE years >= 3
RETURN b
ORDER BY b.node1, b.node2
```
```

--------------------------------

### Node Query Example

Source: https://docs.kinetica.com/7.2/sql/graph

Example of a query to find specific nodes within the graph based on defined criteria.

```APIDOC
## Node Query Example

### Description
This query finds all nodes with the label 'MANAGER' in the 'employee_graph' that belong to department 6 and have a salary greater than 150000. The results are ordered by the node identifier.

### Method
GRAPH

### Endpoint
N/A (This is a query statement)

### Query
```
GRAPH employee_graph
MATCH (a:MANAGER {"dept_id": 6})
WHERE salary > 150000
RETURN a
ORDER BY a.node
```
```

--------------------------------

### Create Directed Graph with Labels from Tables

Source: https://docs.kinetica.com/7.2/sql/graph

This example demonstrates creating a directed graph where node and edge labels are derived from columns in various tables. It implicitly labels nodes and explicitly assigns labels based on genre, profession, and participation.

```sql
CREATE DIRECTED GRAPH imdb
(
    NODES => INPUT_TABLES
    (
        -- Implicit labeling of movie nodes with the LABEL_KEY 'movie', as all movies have an associated genre
        (SELECT DISTINCT genre AS LABEL, 'movie' AS LABEL_KEY FROM movie),
        -- Implicit labeling of person nodes with the LABEL_KEY 'person', as all persons have an associated profession
        (SELECT DISTINCT profession AS LABEL, 'person' AS LABEL_KEY FROM person),

        -- Give each movie node a genre label; the LABEL_KEY 'movie' will also be associated with each
        (SELECT title AS NAME, genre AS LABEL FROM movie),
        -- Give each person node a profession label; the LABEL_KEY 'person' will also be associated with each
        (SELECT name AS NAME, profession AS LABEL FROM person),

        (SELECT DISTINCT CHAR64(YEAR(DOB)) AS NAME, CHAR64('year') AS LABEL FROM person),
        (SELECT DISTINCT CHAR64(released) AS NAME, CHAR64('year') AS LABEL FROM movie)
    ),
    EDGES => INPUT_TABLES
    (
        (SELECT DISTINCT participation AS LABEL, 'participation' AS LABEL_KEY FROM imdb),

        (SELECT person AS NODE1_NAME, film AS NODE2_NAME, participation AS LABEL FROM imdb),
        (SELECT title AS NODE1_NAME, CHAR64(released) AS NODE2_NAME, CHAR64('released') AS LABEL FROM movie),
        (SELECT name AS NODE1_NAME, CHAR64(YEAR(dob)) AS NODE2_NAME, CHAR64('born') AS LABEL FROM person)
    ),
    OPTIONS => KV_PAIRS(label_delimiter = ':', graph_table = 'imdb_graph_table')
)
```

--------------------------------

### Remove Partition Example

Source: https://docs.kinetica.com/7.2/sql/ddl

Example of removing a partition named 'order_2017' from the 'customer_order_range_partition_by_year' table.

```sql
ALTER TABLE example.customer_order_range_partition_by_year
REMOVE PARTITION order_2017
```

--------------------------------

### ASOF JOIN Example for Matching Trades to Opening Prices

Source: https://docs.kinetica.com/7.2/sql/query

This example demonstrates joining stock trades with their opening prices, finding opening records within 24 hours prior to the trade. It uses the ASOF function within a LEFT JOIN clause to include trades without a matching opening price record.

```sql
SELECT
    t.id,
    t.dt AS execution_dt,
    q.open_dt AS quote_dt,
    t.price AS execution_price,
    q.open_price
FROM
    example.trades t
    LEFT JOIN example.quotes q ON
        t.ticker = q.symbol AND
        ASOF(t.dt, q.open_dt, INTERVAL '-1' DAY, INTERVAL '0' DAY, MAX)
```

--------------------------------

### REGEXP_INSTR - Find Regex Match Position

Source: https://docs.kinetica.com/7.2/sql/query

Returns the starting position (1-based) of a regular expression match within a string. It supports specifying the starting position, occurrence, whether to return the start or end of the match, and matching modes.

```APIDOC
## REGEXP_INSTR / expr, regex[, position[, occurrence[, begin_end[, mode[, group]]]]]

### Description
Returns the starting position (1-based) in `expr` where a `regex` match is found. If no match is found, `0` is returned.

### Syntax
`REGEXP_INSTR(expr, regex[, position[, occurrence[, begin_end[, mode[, group]]]]])`

### Parameters
- **expr** (string) - The string to search within.
- **regex** (string) - The regular expression pattern to match. Must be a string literal with 256 characters or fewer. POSIX-compliant regular expressions are generally supported. The escape character for literal wildcards in `expr` is `\`.
- **position** (integer, optional) - The position in `expr` to start searching. The first character is position `1`. Defaults to `1`.
- **occurrence** (integer, optional) - Specifies which occurrence of the `regex` match is desired (e.g., `2` for the second occurrence). Matches do not overlap. Defaults to `1`.
- **begin_end** (integer, optional) - Specifies if the beginning (`0`, default) or ending (`1`) position of the match is desired. Position after the end of the match is returned if `1`.
- **mode** (string, optional) - A string specifying matching behavior. Defaults to empty (default behavior). See `REGEXP_LIKE` for supported mode flags.
- **group** (integer, optional) - Specifies which regular expression group's (parentheses within `regex`) beginning/ending position to return. `0` uses the entire matched expression. `1` through `9` correspond to the 1st through 9th group. Defaults to `0`.

### Examples
```sql
REGEXP_INSTR('abcdefg', 'bc')                      -- Returns 2
REGEXP_INSTR('abcdefg', 'ab', 2)                   -- Returns 0
REGEXP_INSTR('abcabc', 'a', 1, 2)                  -- Returns 4
REGEXP_INSTR('abcabc', 'abc', 1, 1, 1)             -- Returns 4
REGEXP_INSTR('abcabc', 'B C', 1, 1, 0, 'ix')      -- Returns 2
REGEXP_INSTR('abcdefg', '(C(.(.)))', 1, 1, 0, 'i', 3) -- Returns 5
```
```

--------------------------------

### File Header Format Example (Simple)

Source: https://docs.kinetica.com/7.2/sql/load

Illustrates a simple comma-delimited header row format for data files being loaded into Kinetica. This format assumes column names are listed directly, separated by commas.

```text
id,category,name,description,stock

```

--------------------------------

### GRANT Read on Schemas to Role Example

Source: https://docs.kinetica.com/7.2/sql/security

An example of granting SELECT privilege on all schemas to the 'auditor' role.

```sql
GRANT SELECT ON * TO auditor
```

--------------------------------

### SQL Column Definition Examples

Source: https://docs.kinetica.com/7.2/sql/ddl

Provides practical examples of valid column definitions in Kinetica SQL. These examples illustrate how to specify column types, sizes, properties like SHARD_KEY and TEXT_SEARCH, and data types such as INTEGER, VARCHAR, IPV4, and DECIMAL.

```SQL
id INTEGER(SHARD_KEY)            -- id is an integer and the table's shard key
name VARCHAR(64, TEXT_SEARCH)    -- name is a 64-byte limited string and text-searchable
ip IPV4                          -- ip is a string in IPv4 format
cost DECIMAL(10, 2)              -- cost is able to hold an 8.2 decimal
```

--------------------------------

### Kinetica Execute Function Example

Source: https://docs.kinetica.com/7.2/sql/udf

Demonstrates the execution of a Kinetica UDF named `udf_st`. It shows how to specify input and output tables, pass parameters, and include a hint to save runtime statistics. This is a standard way to invoke UDFs for data processing.

```SQL
EXECUTE FUNCTION udf_st /* KI_HINT_SAVE_UDF_STATS */
(
    INPUT_TABLE_NAMES => INPUT_TABLE(example.udf_st_in),
    OUTPUT_TABLE_NAMES => OUTPUT_TABLES('example.udf_st_out'),
    PARAMS => KV_PAIRS(sentence = 'I live in a neighborhood in a small town.')
)
```

--------------------------------

### FILTER_BY_STRING Example

Source: https://docs.kinetica.com/7.2/sql/query

A practical example demonstrating how to use FILTER_BY_STRING to find specific messages in an event log.

```APIDOC
## FILTER_BY_STRING Example

### Description
This example shows how to retrieve event messages containing the word 'ERROR' from the `example.event_log` table.

### Method
SELECT

### Endpoint
N/A (Table Function)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```sql
SELECT *
FROM TABLE
(
    FILTER_BY_STRING
    (
        TABLE_NAME => INPUT_TABLE(SELECT event_time, message FROM example.event_log),
        COLUMN_NAMES => 'message',
        MODE => 'contains',
        EXPRESSION => 'ERROR'
    )
)
```

### Response
#### Success Response (200)
Returns rows from `example.event_log` where the 'message' column contains 'ERROR'.

#### Response Example
```json
{
  "example": "{ 'event_time': '2023-10-27 10:00:00', 'message': 'System encountered an ERROR.' }"
}
```
```

--------------------------------

### SQL EXPLAIN Statement Example

Source: https://docs.kinetica.com/7.2/sql/query

Provides a practical example of using the EXPLAIN statement to generate an execution plan for a SQL query that aggregates taxi rides between boroughs. This helps in understanding how Kinetica processes complex queries.

```sql
EXPLAIN
SELECT
    n_begin.ntaname AS boro_begin,
    boro_end,
    COUNT(*) AS total_trips
FROM
(
    SELECT pickup_latitude, pickup_longitude, n_end.ntaname AS boro_end
    FROM demo.nyctaxi t
    JOIN example_geospatial.nyc_neighborhood n_end ON STXY_INTERSECTS(dropoff_longitude, dropoff_latitude, geom)
)
JOIN example_geospatial.nyc_neighborhood n_begin ON STXY_INTERSECTS(pickup_longitude, pickup_latitude, geom)
GROUP BY 1, 2
```

--------------------------------

### Query Employees by Manager Tenure (Edge Query)

Source: https://docs.kinetica.com/7.2/sql/graph

This example shows how to query for employees based on the duration they have been managed by a specific employee. It uses an edge match to find relationships and filters based on edge properties.

```sql
GRAPH employee_graph
MATCH ()-[b:MANAGES {node1: 2}]->()
WHERE years >= 3
RETURN b
ORDER BY b.node1, b.node2
```

--------------------------------

### Create Full Backup SQL Syntax

Source: https://docs.kinetica.com/7.2/sql/backup_restore

This syntax defines how to create a full snapshot backup of specified database objects. It requires a backup name, data sink, and optionally allows specifying objects and backup options. Full backups are the default when the 'CREATE' keyword is used.

```SQL
CREATE BACKUP <backup name>
DATA SINK = <data sink name>
[OBJECTS (<object type> = <object name>[,...])]
[WITH OPTIONS (<backup option name> = '<backup option value>'[,...])]
```

--------------------------------

### File Header Format Example (Kinetica Standard)

Source: https://docs.kinetica.com/7.2/sql/load

Shows the Kinetica standard format for header rows in data files. This format includes column names, their data types, and optional metadata, separated by pipe symbols.

```text
id|int|data,category|string|data|char16,name|string|data|char32,description|string|data|char128|nullable,stock|int|data|nullable

```

--------------------------------

### Reset Table Tier Strategy Example

Source: https://docs.kinetica.com/7.2/sql/ddl

Example of resetting the tier strategy for the 'customer_order' table to its default settings.

```sql
ALTER TABLE example.customer_order
RESET TIER STRATEGY
```
Tokens:
Raw