### Install Protobuf 3.0 Source: https://calcite.apache.org/docs/howto.html Commands to download, configure, and install the Protobuf 3.0 library required for Avatica RPC serialization. ```bash $ wget https://github.com/google/protobuf/releases/download/v3.0.0-beta-1/protobuf-java-3.0.0-beta-1.tar.gz $ tar xf protobuf-java-3.0.0-beta-1.tar.gz && cd protobuf-3.0.0-beta-1 $ ./configure $ make $ sudo make install ``` -------------------------------- ### Build and start the test VM Source: https://calcite.apache.org/docs/howto.html Command to initialize the test dataset environment using Maven. ```bash cd calcite-test-dataset && mvn install ``` -------------------------------- ### Download and build the Calcite CSV example Source: https://calcite.apache.org/docs/tutorial.html Commands to clone the repository and launch the SQL shell for the CSV adapter. ```bash $ git clone https://github.com/apache/calcite.git $ cd calcite/example/csv $ ./sqlline ``` -------------------------------- ### Schema Path Configuration Source: https://calcite.apache.org/docs/model.html Example of defining a SQL path with multiple elements to resolve functions. ```json path: [ ['usr', 'lib'], 'lib' ] ``` ```yaml path: - [usr, lib] - lib ``` -------------------------------- ### Unordered UNION ALL stream results Source: https://calcite.apache.org/docs/stream.html Example showing how UNION ALL on partitioned streams can result in out-of-order output. ```sql SELECT STREAM * FROM Orders WHERE productId = 10 UNION ALL SELECT STREAM * FROM Orders WHERE productId = 30; rowtime | productId | orderId | units ----------+-----------+---------+------- 10:17:05 | 10 | 6 | 1 10:17:00 | 30 | 5 | 4 10:18:07 | 30 | 8 | 20 11:02:00 | 10 | 9 | 6 11:04:00 | 10 | 10 | 1 11:24:11 | 10 | 12 | 4 ``` -------------------------------- ### Compare Sliding Window Averages Source: https://calcite.apache.org/docs/stream.html This example compares the average order size over a short period (10 minutes) with a longer period (7 days) for each product. It uses named windows for conciseness. ```sql SELECT STREAM * FROM ( SELECT STREAM rowtime, productId, units, AVG(units) OVER product (RANGE INTERVAL '10' MINUTE PRECEDING) AS m10, AVG(units) OVER product (RANGE INTERVAL '7' DAY PRECEDING) AS d7 FROM Orders WINDOW product AS ( ORDER BY rowtime PARTITION BY productId)) WHERE m10 > d7; ``` -------------------------------- ### JSON_SET SQL Example Source: https://calcite.apache.org/docs/reference.html Sets values in a JSON document. ```SQL SELECT JSON_SET(v, '$.a', 10, '$.c', '[1]') AS c1, JSON_SET(v, '$', 10, '$.c', '[1]') AS c2 FROM (VALUES ('{\"a\": 1,\"b\":[2]}')) AS t(v) limit 10; ``` -------------------------------- ### SQL Query for File Adapter Source: https://calcite.apache.org/docs/file_adapter.html Example SQL query joining two tables defined via the file adapter. ```sql select count(*) "City Count", sum(100 * c."Population" / s."Population") "Pct State Population" from "Cities" c, "States" s where c."State" = s."State" and s."State" = 'California'; ``` -------------------------------- ### Query a view Source: https://calcite.apache.org/docs/tutorial.html Example of querying a defined view as if it were a standard table. ```sql sqlline> SELECT e.name, d.name FROM female_emps AS e JOIN depts AS d on e.deptno = d.deptno; +--------+------------+ | NAME | NAME | +--------+------------+ | Wilma | Marketing | +--------+------------+ ``` -------------------------------- ### Execute SQL Query Source: https://calcite.apache.org/docs/elasticsearch_adapter.html Example SQL query to fetch data from the 'usa' index. ```sql sqlline> SELECT * from "usa"; ``` -------------------------------- ### Calcite InnoDB Adapter vs MySQL Server Architecture Source: https://calcite.apache.org/docs/innodb_adapter.html Illustrates the architectural difference between the Calcite InnoDB Adapter and a traditional MySQL Server setup, highlighting the direct access to .ibd files by the adapter. ```text SQL query | | / \ --------- --------- | | v v +-------------------------+ +------------------------+ | MySQL Server | | Calcite InnoDB Adapter | | | +------------------------+ | +---------------------+ | +--------------------+ | |InnoDB Storage Engine| | | innodb-java-reader | | +---------------------+ | +--------------------+ +-------------------------+ -------------------- File System -------------------- +------------+ +-----+ | .ibd files | ... | | InnoDB Data files +------------+ +-----+ ``` -------------------------------- ### Execute SQL query on Cassandra Source: https://calcite.apache.org/docs/cassandra_adapter.html Example of a SQL query executed against the Cassandra adapter, demonstrating filtering and ordering. ```sql sqlline> SELECT "tweet_id" FROM "timeline" WHERE "username" = 'JmuhsAaMdw' ORDER BY "time" DESC LIMIT 1; ``` -------------------------------- ### Perform Session Windowing in SQL Source: https://calcite.apache.org/docs/reference.html Applies a 20-minute session window to the orders table partitioned by product. The first example uses positional arguments, while the second uses named parameters. ```sql SELECT * FROM TABLE( SESSION( TABLE orders PARTITION BY product, DESCRIPTOR(rowtime), INTERVAL '20' MINUTE)); -- or with the named params -- note: the DATA param must be the first SELECT * FROM TABLE( SESSION( DATA => TABLE orders PARTITION BY product, TIMECOL => DESCRIPTOR(rowtime), SIZE => INTERVAL '20' MINUTE)); ``` -------------------------------- ### Connect and Query Druid via SQLLine Source: https://calcite.apache.org/docs/druid_adapter.html Example of connecting to a Druid model using the SQLLine tool and executing a SQL query. ```sql $ ./sqlline sqlline> !connect jdbc:calcite:model=druid/src/test/resources/druid-wiki-model.json admin admin sqlline> select "countryName", cast(count(*) as integer) as c from "wiki" group by "countryName" order by c desc limit 5; +----------------+------------+ | countryName | C | +----------------+------------+ | | 35445 | | United States | 528 | | Italy | 256 | | United Kingdom | 234 | | France | 205 | +----------------+------------+ 5 rows selected (0.279 seconds) sqlline> ``` -------------------------------- ### Query process information with ps Source: https://calcite.apache.org/docs/os_adapter.html Examples of querying process data using sqlsh, including handling reserved words and performing aggregations. ```sql $ sqlsh select distinct ps.\`user\` from ps avahi root jhyde syslog nobody daemon ``` ```bash $ ps aux | awk '{print $1}' | sort | uniq -c | sort -nr | head -3 ``` ```sql $ ./sqlsh select count\(\*\), ps.\`user\` from ps group by ps.\`user\` order by 1 desc limit 3 185 root 69 jhyde 2 avahi ``` -------------------------------- ### HTML Table Source Source: https://calcite.apache.org/docs/file_adapter.html Example HTML structure used as a data source for the file adapter. ```html
EMPNO NAME DEPTNO
100 Fred 30
110 Eric 20
110 John 40
120 Wilma 20
130 Alice 40
``` -------------------------------- ### Connect to Custom Schema (Cassandra Example) Source: https://calcite.apache.org/docs/adapter.html Connect to a custom schema using a specified schema factory. Adapter-specific parameters are prefixed with 'schema.'. ```jdbc jdbc:calcite:schemaFactory=org.apache.calcite.adapter.cassandra.CassandraSchemaFactory; schema.host=localhost; schema.keyspace=twissandra ``` -------------------------------- ### JSON Example Data Source: https://calcite.apache.org/docs/reference.html Sample JSON structure used for demonstrating query function behaviors. ```json {"a": "[1,2]", "b": [1,2], "c": "hi"} ``` -------------------------------- ### Calcite InnoDB Adapter Model File Configuration Source: https://calcite.apache.org/docs/innodb_adapter.html Example of a Calcite model JSON file for configuring the InnoDB adapter. Specify the SQL DDL file path and the base path for InnoDB data files. ```json { "version": "1.0", "defaultSchema": "scott", "schemas": [ { "name": "scott", "type": "custom", "factory": "org.apache.calcite.adapter.innodb.InnodbSchemaFactory", "operand": { "sqlFilePath": [ "/path/scott.sql" ], "ibdDataFileBasePath": "/usr/local/mysql/data/scott" } } ] } ``` -------------------------------- ### SQL query with aggregation and ordering Source: https://calcite.apache.org/docs/geode_adapter.html Example of a SQL query that performs aggregation (SUM) and ordering on Geode data. The Geode adapter can push these operations down to Geode for efficient processing. ```sql SELECT "yearPublished", SUM("retailCost") AS "totalCost" FROM "TEST"."BookMaster" GROUP BY "yearPublished" ORDER BY "totalCost"; ``` -------------------------------- ### Connect to JDBC Data Source with Calcite Source: https://calcite.apache.org/docs/index.html This example shows how to configure Calcite to connect to a JDBC data source. Replace placeholder credentials and URL with your actual database details. This allows Calcite to query data residing in external databases. ```java Class.forName("com.mysql.jdbc.Driver"); BasicDataSource dataSource = new BasicDataSource(); dataSource.setUrl("jdbc:mysql://localhost"); dataSource.setUsername("username"); dataSource.setPassword("password"); Schema schema = JdbcSchema.create(rootSchema, "hr", dataSource, null, "name"); ``` -------------------------------- ### DECODE SQL Example Source: https://calcite.apache.org/docs/reference.html Performs conditional mapping of values. ```SQL SELECT DECODE(f1, 1, 'aa', 2, 'bb', 3, 'cc', 4, 'dd', 'ee') as c1, DECODE(f2, 1, 'aa', 2, 'bb', 3, 'cc', 4, 'dd', 'ee') as c2, DECODE(f3, 1, 'aa', 2, 'bb', 3, 'cc', 4, 'dd', 'ee') as c3, DECODE(f4, 1, 'aa', 2, 'bb', 3, 'cc', 4, 'dd', 'ee') as c4, DECODE(f5, 1, 'aa', 2, 'bb', 3, 'cc', 4, 'dd', 'ee') as c5 FROM (VALUES (1, 2, 3, 4, 5)) AS t(f1, f2, f3, f4, f5); ``` -------------------------------- ### JSON_KEYS SQL Example Source: https://calcite.apache.org/docs/reference.html Returns the keys of a JSON object. ```SQL SELECT JSON_KEYS(v) AS c1, JSON_KEYS(v, 'lax $.a') AS c2, JSON_KEYS(v, 'lax $.b') AS c2, JSON_KEYS(v, 'strict $.a[0]') AS c3, JSON_KEYS(v, 'strict $.a[1]') AS c4 FROM (VALUES ('{"a": [10, true],"b": {"c": 30}}')) AS t(v) LIMIT 10; ``` -------------------------------- ### Launch sqlline with file adapter Source: https://calcite.apache.org/docs/file_adapter.html Commands to list files and connect to the Calcite file adapter via sqlline. ```bash $ ls file/src/test/resources/sales-json -rw-r--r-- 1 jhyde jhyde 62 Mar 15 10:16 DEPTS.json $ ./sqlline -u "jdbc:calcite:schemaFactory=org.apache.calcite.adapter.file.FileSchemaFactory;schema.directory=file/src/test/resources/sales-json" sqlline> !tables +-----------+-------------+------------+------------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | +-----------+-------------+------------+------------+ | | adhoc | DATE | TABLE | | | adhoc | DEPTS | TABLE | | | adhoc | EMPS | TABLE | | | adhoc | EMPTY | TABLE | | | adhoc | SDEPTS | TABLE | +-----------+-------------+------------+------------+ sqlline> select distinct deptno from depts; +--------+ | DEPTNO | +--------+ | 20 | | 10 | | 30 | +--------+ 3 rows selected (0.985 seconds) ``` -------------------------------- ### JSON_INSERT SQL Example Source: https://calcite.apache.org/docs/reference.html Inserts data into a JSON document. ```SQL SELECT JSON_INSERT(v, '$.a', 10, '$.c', '[1]') AS c1, JSON_INSERT(v, '$', 10, '$.c', '[1]') AS c2 FROM (VALUES ('{"a": [10, true]}')) AS t(v) LIMIT 10; ``` -------------------------------- ### Execute release candidate build Source: https://calcite.apache.org/docs/howto.html Commands to prepare the release candidate, including GPG configuration and pushing to ASF servers. ```bash # Tell GPG how to read a password from your terminal export GPG_TTY=$(tty) # Make sure that there are no junk files in the sandbox git clean -xn # Dry run the release candidate (push to asf-like-environment) ./gradlew prepareVote -Prc=0 # Push release candidate to ASF servers # If you prefer to use GitHub account, change pushRepositoryProvider to GITHUB ./gradlew prepareVote -Prc=0 -Pasf -Pasf.git.pushRepositoryProvider=GITBOX ``` -------------------------------- ### JSON_TYPE SQL Example Source: https://calcite.apache.org/docs/reference.html Returns the type of a JSON value. ```SQL SELECT JSON_TYPE(v) AS c1, JSON_TYPE(JSON_VALUE(v, 'lax $.b' ERROR ON ERROR)) AS c2, JSON_TYPE(JSON_VALUE(v, 'strict $.a[0]' ERROR ON ERROR)) AS c3, JSON_TYPE(JSON_VALUE(v, 'strict $.a[1]' ERROR ON ERROR)) AS c4 FROM (VALUES ('{"a": [10, true],"b": "[10, true]"}')) AS t(v) LIMIT 10; ``` -------------------------------- ### Connecting to Calcite with sqlline and Listing Tables Source: https://calcite.apache.org/docs/druid_adapter.html This demonstrates connecting to Calcite using sqlline with a Druid schema and then listing the available tables. ```bash $ ./sqlline sqlline> !connect jdbc:calcite:schemaFactory=org.apache.calcite.adapter.druid.DruidSchemaFactory admin admin sqlline> !tables +-----------+-------------+------------+--------------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | +-----------+-------------+------------+--------------+ | | adhoc | foodmart | TABLE | | | adhoc | wikiticker | TABLE | | | metadata | COLUMNS | SYSTEM_TABLE | | | metadata | TABLES | SYSTEM_TABLE | +-----------+-------------+------------+--------------+ ``` -------------------------------- ### TRANSLATE SQL Example Source: https://calcite.apache.org/docs/reference.html Replaces characters in a string based on a mapping. ```SQL SELECT TRANSLATE('Aa*Bb*Cc''D*d', ' */''%', '_') as c1, TRANSLATE('Aa/Bb/Cc''D/d', ' */''%', '_') as c2, TRANSLATE('Aa Bb Cc''D d', ' */''%', '_') as c3, TRANSLATE('Aa%Bb%Cc''D%d', ' */''%', '_') as c4 FROM (VALUES (true)) AS t(f0); ``` -------------------------------- ### JSON_REPLACE SQL Example Source: https://calcite.apache.org/docs/reference.html Replaces existing values in a JSON document. ```SQL SELECT JSON_REPLACE(v, '$.a', 10, '$.c', '[1]') AS c1, JSON_REPLACE(v, '$', 10, '$.c', '[1]') AS c2 FROM (VALUES ('{\"a\": 1,\"b\":[2]}')) AS t(v) limit 10; ``` -------------------------------- ### Query and Explain Plan Source: https://calcite.apache.org/docs/lattice.html Execute a query and inspect its execution plan to verify usage of materialized tiles. ```sql sqlline> select "the_year","the_month", count(*) as c . . . .> from "sales_fact_1997" . . . .> join "time_by_day" using ("time_id") . . . .> group by "the_year","the_month"; +----------+-----------+------+ | the_year | the_month | C | +----------+-----------+------+ | 1997 | September | 6663 | | 1997 | April | 6590 | | 1997 | January | 7034 | | 1997 | June | 6912 | | 1997 | August | 7038 | | 1997 | February | 6844 | | 1997 | March | 7710 | | 1997 | October | 6479 | | 1997 | May | 6866 | | 1997 | December | 8717 | | 1997 | July | 7752 | | 1997 | November | 8232 | +----------+-----------+------+ 12 rows selected (0.147 seconds) sqlline> explain plan for . . . .> select "the_year","the_month", count(*) as c . . . .> from "sales_fact_1997" . . . .> join "time_by_day" using ("time_id") . . . .> group by "the_year","the_month"; +--------------------------------------------------------------------------------+ | PLAN | +--------------------------------------------------------------------------------+ | EnumerableCalc(expr#0..2=[{inputs}], the_year=[$t1], the_month=[$t0], C=[$t2]) | | EnumerableAggregate(group=[{3, 4}], C=[$SUM0($7)]) | | EnumerableTableScan(table=[[adhoc, m{16, 17, 27, 31, 32, 36, 37}]]) | +--------------------------------------------------------------------------------+ ``` -------------------------------- ### JSON_DEPTH SQL Example Source: https://calcite.apache.org/docs/reference.html Returns the maximum depth of a JSON document. ```SQL SELECT JSON_DEPTH(v) AS c1, JSON_DEPTH(JSON_VALUE(v, 'lax $.b' ERROR ON ERROR)) AS c2, JSON_DEPTH(JSON_VALUE(v, 'strict $.a[0]' ERROR ON ERROR)) AS c3, JSON_DEPTH(JSON_VALUE(v, 'strict $.a[1]' ERROR ON ERROR)) AS c4 FROM (VALUES ('{"a": [10, true],"b": "[10, true]"}')) AS t(v) LIMIT 10; ``` -------------------------------- ### Define PrepareAndExecuteRequest Message Source: https://calcite.apache.org/docs/avatica_protobuf_reference.html Use this message as a shorthand to create a Statement and fetch the first batch of results. It requires connection ID, statement ID, SQL query, and row count limits. ```protobuf message PrepareAndExecuteRequest { string connection_id = 1; uint32 statement_id = 4; string sql = 2; uint64 max_row_count = 3; // Deprecated! int64 max_rows_total = 5; int32 first_frame_max_size = 6; } ``` -------------------------------- ### JSON_STORAGE_SIZE SQL Example Source: https://calcite.apache.org/docs/reference.html Returns the storage size of a JSON document in bytes. ```SQL SELECT JSON_STORAGE_SIZE('[100, \"sakila\", [1, 3, 5], 425.05]') AS c1, JSON_STORAGE_SIZE('{\"a\": 10, \"b\": \"a\", \"c\": \"[1, 3, 5, 7]\"}') AS c2, JSON_STORAGE_SIZE('{\"a\": 10, \"b\": \"xyz\", \"c\": \"[1, 3, 5, 7]\"}') AS c3, JSON_STORAGE_SIZE('[100, \"json\", [[10, 20, 30], 3, 5], 425.05]') AS c4 limit 10; ``` -------------------------------- ### Apply SQL Hints in Queries Source: https://calcite.apache.org/docs/reference.html Demonstrates the placement of query hints after the SELECT keyword and table hints after table references. ```sql SELECT /*+ hint1, hint2(a=1, b=2) */ ... FROM tableName /*+ hint3(5, 'x') */ JOIN tableName /*+ hint4(c=id), hint5 */ ... ``` -------------------------------- ### JSON_REMOVE SQL Example Source: https://calcite.apache.org/docs/reference.html Removes data from a JSON document at the specified path. ```SQL SELECT JSON_REMOVE(v, '$[1]') AS c1 FROM (VALUES ('["a", ["b", "c"], "d"]')) AS t(v) LIMIT 10; ``` -------------------------------- ### JSON_LENGTH SQL Example Source: https://calcite.apache.org/docs/reference.html Returns the length of a JSON document or a specific path within it. ```SQL SELECT JSON_LENGTH(v) AS c1, JSON_LENGTH(v, 'lax $.a') AS c2, JSON_LENGTH(v, 'strict $.a[0]') AS c3, JSON_LENGTH(v, 'strict $.a[1]') AS c4 FROM (VALUES ('{"a": [10, true]}')) AS t(v) LIMIT 10; ``` -------------------------------- ### Explain Query Plan Source: https://calcite.apache.org/docs/druid_adapter.html Use the EXPLAIN PLAN command to inspect how Calcite translates and optimizes SQL queries for Druid. ```sql sqlline> !set outputformat csv sqlline> explain plan for select "countryName", cast(count(*) as integer) as c from "wiki" group by "countryName" order by c desc limit 5; 'PLAN' 'EnumerableInterpreter BindableProject(countryName=[$0], C=[CAST($1):INTEGER NOT NULL]) BindableSort(sort0=[$1], dir0=[DESC], fetch=[5]) DruidQuery(table=[[wiki, wiki]], groups=[{4}], aggs=[[COUNT()]]) ' 1 row selected (0.024 seconds) ``` -------------------------------- ### ROW_NUMBER() OVER window Source: https://calcite.apache.org/docs/reference.html Assigns a sequential integer to each row within its partition, starting from 1. ```sql ROW_NUMBER() OVER window ``` -------------------------------- ### Configure Gradle credentials for release Source: https://calcite.apache.org/docs/howto.html Define signing and repository credentials in ~/.gradle/gradle.properties. Ensure file permissions are set to 600 for security. ```properties useGpgCmd=true signing.gnupg.executable=gpg signing.gnupg.useLegacyGpg=false signing.gnupg.keyName=0xXXXXXXXX signing.gnupg.passphrase=xxx asfSvnUsername=jhyde asfSvnPassword=xxx asfGitSourceUsername=julianhyde asfGitSourcePassword=xxx asfNexusUsername=jhyde asfNexusPassword=xxx asfCommitterId=jhyde asfTestSvnPassword=test asfTestSvnUsername=test asfTestGitSourceUsername=test asfTestGitSourcePassword=test asfTestNexusUsername=test asfTestNexusPassword=test ``` -------------------------------- ### PrepareAndExecuteRequest Source: https://calcite.apache.org/docs/avatica_json_reference.html Short-hand to create a statement and fetch the first batch of results. ```APIDOC ## POST /prepareAndExecute ### Description This request is used as a short-hand for create a Statement and fetching the first batch of results in a single call without any parameter substitution. ### Request Body - **request** (string) - Required - Must be "prepareAndExecute" - **connectionId** (string) - Required - The identifier for the connection to use. - **statementId** (integer) - Required - The identifier for the statement created by the above connection to use. - **sql** (string) - Required - A SQL statement. - **maxRowCount** (long) - Required - The maximum number of rows returned in the response. ### Request Example { "request": "prepareAndExecute", "connectionId": "000000-0000-0000-00000000", "statementId": 12345, "sql": "SELECT * FROM table", "maxRowCount": 100 } ``` -------------------------------- ### Connect to Cassandra via sqlline Source: https://calcite.apache.org/docs/cassandra_adapter.html Command to launch sqlline and establish a JDBC connection using the model file. ```bash $ ./sqlline sqlline> !connect jdbc:calcite:model=model.json admin admin ``` -------------------------------- ### Build Calcite from Source Distribution Source: https://calcite.apache.org/docs/howto.html Use this command to build Calcite after unpacking the source distribution. Ensure Java and Gradle are on your PATH. ```bash $ tar xvfz apache-calcite-1.41.0-src.tar.gz $ cd apache-calcite-1.41.0-src $ gradle build ``` -------------------------------- ### Querying stream history Source: https://calcite.apache.org/docs/stream.html Example of running a relational query on the history of a stream using a time interval. ```sql SELECT productId, count(*) FROM Orders WHERE rowtime BETWEEN current_timestamp - INTERVAL '1' HOUR AND current_timestamp; ``` -------------------------------- ### Download Apache Calcite KEYS file Source: https://calcite.apache.org/docs/howto.html Fetches the KEYS file from the Apache distribution repository, which contains public keys for verifying signatures. ```bash # Check keys curl -O https://dist.apache.org/repos/dist/release/calcite/KEYS ``` -------------------------------- ### Smoke-test sqlline with Spatial and Oracle functions Source: https://calcite.apache.org/docs/howto.html Verify the build by connecting to the Calcite JDBC driver and executing a sample query. ```bash $ ./sqlline > !connect jdbc:calcite:fun=spatial,oracle "sa" "" SELECT NVL(ST_Is3D(ST_PointFromText('POINT(-71.064544 42.28787)')), TRUE); +--------+ | EXPR$0 | +--------+ | false | +--------+ 1 row selected (0.039 seconds) > !quit ``` -------------------------------- ### Example JSON schema definition Source: https://calcite.apache.org/docs/file_adapter.html A sample JSON file structure where columns are inferred from key-value pairs. ```json [ { "DEPTNO": 10, "NAME": "Sales" }, { "DEPTNO": 20, "NAME": "Marketing" }, { "DEPTNO": 30, "NAME": "Accounts" } ] ``` -------------------------------- ### Function Call with Named Arguments Source: https://calcite.apache.org/docs/reference.html Demonstrates calling a function using named arguments with the '=>' syntax. If one argument is named, all must be named. Arguments can be in any order, but each parameter must be specified exactly once, and all non-optional parameters must have a value. ```sql f(c => 3, d => 1, a => 0) ``` -------------------------------- ### Invoke a user-defined table function Source: https://calcite.apache.org/docs/adapter.html Example of using a table function named Ramp within a SQL FROM clause. ```sql SELECT * FROM TABLE(Ramp(3, 4)) ``` -------------------------------- ### Connect to InnoDB data file using sqlline Source: https://calcite.apache.org/docs/innodb_adapter.html Command to connect to an InnoDB data file using sqlline, specifying the Calcite model file for configuration. ```bash sqlline> !connect jdbc:calcite:model=model.json admin admin ``` -------------------------------- ### Grouped Auxiliary Functions Source: https://calcite.apache.org/docs/reference.html These functions provide access to the start and end times of windows defined by grouped window functions. ```APIDOC ### Grouped Auxiliary Functions Allow access to properties of windows defined by grouped window functions. #### HOP_END(expression, slide, size [, time ]) Returns the value of `expression` at the end of the window defined by a `HOP` function call. #### HOP_START(expression, slide, size [, time ]) Returns the value of `expression` at the beginning of the window defined by a `HOP` function call. #### SESSION_END(expression, interval [, time]) Returns the value of `expression` at the end of the window defined by a `SESSION` function call. #### SESSION_START(expression, interval [, time]) Returns the value of `expression` at the beginning of the window defined by a `SESSION` function call. #### TUMBLE_END(expression, interval [, time ]) Returns the value of `expression` at the end of the window defined by a `TUMBLE` function call. #### TUMBLE_START(expression, interval [, time ]) Returns the value of `expression` at the beginning of the window defined by a `TUMBLE` function call. ``` -------------------------------- ### Inspect table schema with explain Source: https://calcite.apache.org/docs/os_adapter.html Displays the column structure of a table using the explain command. ```bash $ sqlsh explain plan with type for select \* from du size_k BIGINT NOT NULL, path VARCHAR NOT NULL, size_b BIGINT NOT NULL ``` -------------------------------- ### Get Catalogs Request Source: https://calcite.apache.org/docs/avatica_json_reference.html Use this JSON object to request a list of available catalog names from the database. Requires a connection ID. ```json { "request": "getCatalogs", "connectionId": "000000-0000-0000-00000000" } ``` -------------------------------- ### Implement Calcite Adapter Context Source: https://calcite.apache.org/docs/howto.html This Java code demonstrates the basic structure for implementing a custom Calcite adapter by extending CalcitePrepare.Context and providing implementations for getTypeFactory and getRootSchema. ```java import org.apache.calcite.adapter.java.JavaTypeFactory; import org.apache.calcite.jdbc.CalcitePrepare; import org.apache.calcite.jdbc.CalciteSchema; public class AdapterContext implements CalcitePrepare.Context { @Override public JavaTypeFactory getTypeFactory() { // adapter implementation return typeFactory; } @Override public CalciteSchema getRootSchema() { // adapter implementation return rootSchema; } } ``` -------------------------------- ### Compare query plans with and without optimization Source: https://calcite.apache.org/docs/tutorial.html Demonstrates the difference in query plans when using a standard table scan versus an optimized CsvTableScan. ```sql sqlline> !connect jdbc:calcite:model=src/test/resources/model.json admin admin sqlline> explain plan for select name from emps; +-----------------------------------------------------+ | PLAN | +-----------------------------------------------------+ | EnumerableCalc(expr#0..9=[{inputs}], NAME=[$t1]) | | EnumerableTableScan(table=[[SALES, EMPS]]) | +-----------------------------------------------------+ sqlline> !connect jdbc:calcite:model=src/test/resources/smart.json admin admin sqlline> explain plan for select name from emps; +-----------------------------------------------------+ | PLAN | +-----------------------------------------------------+ | CsvTableScan(table=[[SALES, EMPS]], fields=[[1]]) | +-----------------------------------------------------+ ``` -------------------------------- ### Process stdin with sqlsh Source: https://calcite.apache.org/docs/os_adapter.html Demonstrates piping standard input into sqlsh for processing. ```bash $ (echo cats; echo and dogs) | cat -n - 1 cats 2 and dogs ``` ```bash $ (echo cats; echo and dogs) | ./sqlsh select \* from stdin 1 cats 2 and dogs ``` -------------------------------- ### Query all employees from EMP table Source: https://calcite.apache.org/docs/innodb_adapter.html Example SQL query to retrieve all employee records from the 'EMP' table using the Calcite InnoDB adapter. ```sql sqlline> select empno, ename, job, age, mgr from "EMP"; ``` -------------------------------- ### Execute Gradle build and test commands Source: https://calcite.apache.org/docs/howto.html Standard Gradle tasks for building artifacts, running tests, and verifying code style. ```bash $ ./gradlew assemble # build the artifacts $ ./gradlew build -x test # build the artifacts, verify code style, skip tests $ ./gradlew check # verify code style, execute tests $ ./gradlew test # execute tests $ ./gradlew style # update code formatting (for auto-correctable cases) and verify style $ ./gradlew autostyleCheck checkstyleAll # report code style violations $ ./gradlew -PenableErrorprone classes # verify Java code with Error Prone compiler, requires Java 11 ``` -------------------------------- ### Querying EMP table with Calcite Source: https://calcite.apache.org/docs/innodb_adapter.html Example SQL query to select employee details from the EMP table, filtering by hire date and ordering the results. ```sql sqlline> select deptno,ename,hiredate from "EMP" where hiredate < '2020-01-01' order by hiredate desc; +--------+--------+------------+ | DEPTNO | ENAME | HIREDATE | +--------+--------+------------+ | 20 | ADAMS | 1987-05-23 | | 20 | SCOTT | 1987-04-19 | | 10 | MILLER | 1982-01-23 | | 20 | FORD | 1981-12-03 | | 30 | JAMES | 1981-12-03 | | 10 | KING | 1981-11-17 | | 30 | MARTIN | 1981-09-28 | | 30 | TURNER | 1981-09-08 | | 10 | CLARK | 1981-06-09 | | 30 | WARD | 1981-02-22 | | 30 | ALLEN | 1981-02-20 | | 20 | JONES | 1981-02-04 | | 30 | BLAKE | 1981-01-05 | | 20 | SMITH | 1980-12-17 | +--------+--------+------------+ ``` -------------------------------- ### ASOF JOIN Example Source: https://calcite.apache.org/docs/reference.html Demonstrates a LEFT ASOF JOIN for combining rows based on the closest timestamp match. Ensure the MATCH_CONDITION uses appropriate comparison operators (<, <=, >, >=). ```sql SELECT * FROM left_table LEFT ASOF JOIN right_table MATCH_CONDITION left_table.timecol <= right_table.timecol ON left_table.col = right_table.col ```