### Example SQLAlchemy URI for Dremio in Superset Source: https://docs.dremio.com/cloud/sonar/client-apps/superset An example of a fully formed SQLAlchemy URI for connecting Superset to Dremio. This example includes a placeholder PAT and enables SSL encryption. ```sql dremio+flight://data.dremio.cloud:443/?token=dOOfxnJlTnebGu7Beta9NOT-A-REAL-PATyfOoNbJwEMep7UjkQu0JTsFXpYGm==&UseEncryption=true ``` -------------------------------- ### CREATE TABLE Examples Source: https://docs.dremio.com/cloud/reference/sql/commands/create-table Provides examples of creating tables in Dremio Cloud, including specifying data, using branches, creating struct types, partitioning, and enforcing nullability. ```APIDOC ## CREATE TABLE Examples ### Description Examples of creating tables in Dremio Cloud. ### Method SQL Command ### Endpoint N/A ### Parameters N/A ### Request Example ```sql -- Create a table from a branch CREATE TABLE employees (PersonID int, LastName varchar, FirstName varchar, Address varchar, City varchar) AT BRANCH main -- Create a table CREATE TABLE employees (name VARCHAR, age INT) -- Create a struct table CREATE TABLE struct_type ( a struct ) -- Creating a table and partitioning it by month CREATE TABLE myTable (col1 int, col2 date) PARTITION BY (month(col2)) -- Create a table and enforce column nullability CREATE TABLE my_table (name VARCHAR NOT NULL, age INT NULL, address STRUCT); ``` ### Response #### Success Response (200) Indicates successful table creation. #### Response Example N/A ``` -------------------------------- ### Example cURL Request to Get Billing Transaction Source: https://docs.dremio.com/cloud/reference/api/billing/billing-transactions Demonstrates how to make a GET request to the Dremio Cloud API to fetch a specific billing transaction. This example uses cURL and includes necessary headers for authorization and content type. Replace placeholders with actual IDs and your personal access token. ```curl curl -X GET 'https://api.dremio.cloud/v0/billing/cebe4692-498e-4c04-81ac-8ad04210ca9d/transactions/40bf837e-fd97-4d7d-bb3d-0cefd76ae9ca' \ -H 'Authorization: Bearer ' \ -H 'Content-Type: application/json' ``` -------------------------------- ### Dremio SQL: Example - Create Folder Source: https://docs.dremio.com/cloud/reference/sql/commands/create-folder Example demonstrating the basic SQL command to create a folder named 'myFolder' in the current Dremio Arctic catalog reference. ```sql CREATE FOLDER myFolder ``` -------------------------------- ### Example JSON Response for a COMPLETED Job Source: https://docs.dremio.com/cloud/reference/api/job This is an example of the JSON response received when a job has completed successfully. It includes details such as job state, row count, start and end times, and acceleration information. ```json { "jobState": "COMPLETED", "rowCount": 1003904, "errorMessage": "", "startedAt": "2022-12-13T18:34:14.069Z", "endedAt": "2022-12-13T18:35:09.963Z", "acceleration": { "reflectionRelationships": [ { "datasetId": "ef99ab32-aa47-4f4c-4d1c-d40f8035b846", "reflectionId": "63fd1c83-2319-5962-8a36-60543550580a", "relationship": "CONSIDERED" }, { "datasetId": "596c489c-7949-485b-92a9-c32a4cb51fa2", "reflectionId": "65747723-4133-9e2d-3k86-3d40b26f45ae", "relationship": "MATCHED" } ] }, "queryType": "UI_RUN", "queueName": "LARGE", "queueId": "LARGE", "resourceSchedulingStartedAt": "2022-12-13T18:34:14.977Z", "resourceSchedulingEndedAt": "2022-12-13T18:34:14.995Z", "cancellationReason": "" } ``` -------------------------------- ### VACUUM CATALOG Examples - SQL Source: https://docs.dremio.com/cloud/reference/sql/commands/vacuum-catalog Demonstrates various ways to use the VACUUM CATALOG command in SQL, including basic vacuuming, excluding/including single or multiple tables, and referencing tables at specific branches or tags. ```sql VACUUM CATALOG arctic_catalog; ``` ```sql VACUUM CATALOG arctic_catalog EXCLUDE (t1); ``` ```sql VACUUM CATALOG arctic_catalog EXCLUDE (t1 AT BRANCH dev); ``` ```sql VACUUM CATALOG arctic_catalog EXCLUDE (t1 AT BRANCH dev, t2); ``` ```sql VACUUM CATALOG arctic_catalog EXCLUDE (t1 AT TAG namedTag, t2 AT BRANCH dev); ``` ```sql VACUUM CATALOG arctic_catalog INCLUDE (t1); ``` ```sql VACUUM CATALOG arctic_catalog INCLUDE (t1 AT BRANCH dev); ``` ```sql VACUUM CATALOG arctic_catalog INCLUDE (t1 AT BRANCH dev, t2); ``` ```sql VACUUM CATALOG arctic_catalog INCLUDE (t1 AT TAG namedTag, t2 AT BRANCH dev); ``` -------------------------------- ### Analyze All Columns - Dremio SQL Example Source: https://docs.dremio.com/cloud/reference/sql/commands/analyze-table Example of using the ANALYZE TABLE command to compute statistics for all columns of a specified table. This is useful for getting a general overview of the table's data characteristics. ```sql ANALYZE TABLE Samples."samples.dremio.com"."NYC-taxi-trips" FOR ALL COLUMNS COMPUTE STATISTICS ``` -------------------------------- ### SQL Example: Dropping a Table Source: https://docs.dremio.com/cloud/reference/sql/commands/drop-table This SQL example shows a basic command to drop a table named 'example_table' from the 'demo' schema. This is a straightforward application of the DROP TABLE syntax for immediate table removal. ```sql DROP TABLE demo.example_table ``` -------------------------------- ### List Billing Account Transactions - GET Request Example Source: https://docs.dremio.com/cloud/reference/api/billing/billing-transactions Demonstrates how to list all transactions for a given billing account ID using a GET request to the Dremio Cloud API. The request requires the billing account ID as a path parameter and an authorization token. ```curl curl -X GET 'https://api.dremio.cloud/v0/billing/cebe4692-498e-4c04-81ac-8ad04210ca9d/transactions' \ -H 'Authorization: Bearer ' \ -H 'Content-Type: application/json' ``` -------------------------------- ### Example: Drop a View Source: https://docs.dremio.com/cloud/reference/sql/commands/drop-view An example demonstrating how to drop a view named 'example_view' within the 'demo' schema. ```sql DROP VIEW demo.example_view ``` -------------------------------- ### ARRAY_SLICE Function Examples Source: https://docs.dremio.com/cloud/reference/sql/sql-functions/functions/ARRAY_SLICE Demonstrates the usage of the ARRAY_SLICE function with various start and end index combinations, including positive, negative, and out-of-bounds values. ```sql SELECT ARRAY_SLICE(array_col) -- [0,1,2] SELECT ARRAY_SLICE(array_col, 0, -2) -- [0,1,2,3,4] SELECT ARRAY_SLICE(array_col, -5, -3) -- [2,3] SELECT ARRAY_SLICE(array_col, 10, 12) -- [] ``` -------------------------------- ### Dremio Cloud Flight SQL Sample Client Command Syntax Source: https://docs.dremio.com/cloud/sonar/custom-apps/arrow-flight-sql Provides the command-line syntax for the sample Flight SQL client application. It outlines various options for specifying connection details, commands, and query parameters. ```bash Usage: java -jar flight-sql-sample-client-application.jar -host localhost -port 443 ... -command,--command Method to run -dsv,--disableServerVerification Disable TLS server verification. Defaults to false. -host,--hostname `data.dremio.cloud` for Dremio's US control plane `data.eu.dremio.cloud` for Dremio's European control plane -kstpass,--keyStorePassword The jks keystore password. -kstpath,--keyStorePath Path to the jks keystore. -pat,--personalAccessToken Personal access token -port,--flightport 443 -query,--query The query to run -schema,--schema The schema to use -sp,--sessionProperty Key value pairs of SessionProperty, example: -sp schema='Samples."samples.dremio. com"' -sp key=value -table,--table The table to query -tls,--tls Enable encrypted connection. Defaults to true. ``` -------------------------------- ### Dremio SQL LOCATE Function Examples Source: https://docs.dremio.com/cloud/reference/sql/sql-functions/functions/LOCATE Demonstrates the usage of the LOCATE function in Dremio SQL to find the starting position of a substring within a larger string. It shows cases where the substring is not found, found at the beginning, and found after a specified starting position. The function returns an integer representing the position (1-based) or 0 if not found. ```sql SELECT LOCATE('no','banana') -- 0 ``` ```sql SELECT LOCATE('an','banana') -- 2 ``` ```sql SELECT LOCATE('an','banana', 3) -- 4 ``` -------------------------------- ### Snowflake Login-Password Authentication (JSON) Source: https://docs.dremio.com/cloud/reference/api/catalog/source/source-config Example JSON configuration for authenticating to Snowflake using username and password. This is a common method for simpler setups and requires 'username' and 'password' fields. ```json { "authenticationType": "LOGIN_PASSWORD", "username": "dremio", "password": "myPassword" } ``` -------------------------------- ### CREATE PIPE Example Source: https://docs.dremio.com/cloud/reference/sql/commands/create-pipe Examples demonstrating how to create an autoingest pipe using the COPY INTO command with specified file formats and deduplication settings. ```APIDOC ## Examples ### Create an autoingest pipe from an Amazon S3 source ```sql CREATE PIPE Example_pipe AS COPY INTO Table_one FROM ‘@s3_source/folder’ FILE_FORMAT 'csv' ``` ### Create an autoingest pipe with a custom lookback period ```sql CREATE PIPE Example_pipe DEDUPE_LOOKBACK_PERIOD 5 AS COPY INTO Table_one FROM ‘@/files’ FILE_FORMAT 'csv' ``` ``` -------------------------------- ### BIN Function Documentation Source: https://docs.dremio.com/cloud/reference/sql/sql-functions/functions/BIN This section details the BIN function, its syntax, and provides examples of its usage. ```APIDOC ## BIN Function ### Description Returns the binary representation of an integer expression. ### Syntax `BIN(_expression_ integer) → varchar` - `expression`: An integer expression to encode. ### Examples **Example 1: Positive Integer** ```sql SELECT BIN(100) -- Expected Output: 1100100 ``` **Example 2: Negative Integer** ```sql SELECT BIN(-100) -- Expected Output: 11111111111111111111111110011100 ``` **Example 3: NULL Input** ```sql SELECT BIN(null) -- Expected Output: null ``` ``` -------------------------------- ### SQL: Get Sign of Numeric Expression (Float) Source: https://docs.dremio.com/cloud/reference/sql/sql-functions/functions/SIGN Returns 1 if the numeric expression is positive, -1 if negative, and 0 if zero. This example illustrates the function with a single-precision floating-point number. ```sql SELECT SIGN(0.0) -- 0 ``` -------------------------------- ### SQL: Get Sign of Numeric Expression (Int32) Source: https://docs.dremio.com/cloud/reference/sql/sql-functions/functions/SIGN Returns 1 if the numeric expression is positive, -1 if negative, and 0 if zero. This example demonstrates the function with a 32-bit integer input. ```sql SELECT SIGN(-5) -- -1 ``` -------------------------------- ### Example Dremio SQL Query for Data Analysis Source: https://docs.dremio.com/cloud/tutorials/zero-to-lakehouse/create-table This SQL query aggregates passenger count by pickup day from the 'nyc_trips' table. It demonstrates date part extraction, sum aggregation, grouping, and ordering, providing an example of data analysis within Dremio. ```sql SELECT DATE_PART('DAY', "catalog_name"."my_folder"."nyc_trips"."pickup_datetime") AS "pickup_day", SUM("catalog_name"."my_folder"."nyc_trips"."passenger_count") AS "passenger_count" FROM "catalog_name"."my_folder"."nyc_trips" GROUP BY pickup_day ORDER BY pickup_day; ``` -------------------------------- ### Get Current Date using CURRENT_DATE in SQL Source: https://docs.dremio.com/cloud/reference/sql/sql-functions/functions/CURRENT_DATE This example shows an alternative syntax for retrieving the current system date in SQL by omitting the parentheses. This is a valid shorthand for the CURRENT_DATE function. ```sql SELECT CURRENT_DATE -- 2021-07-02 ``` -------------------------------- ### Create Table in Project Store (SQL) Source: https://docs.dremio.com/cloud/reference/sql/commands/create-table-as This SQL statement creates a new table named 'demo_table' in the project store by selecting all columns from a specified source. It's a basic example of table creation. ```sql CREATE TABLE demo_table AS SELECT * FROM Samples."samples.dremio.com"."zips.json" ``` -------------------------------- ### AI_COMPLETE Function Documentation Source: https://docs.dremio.com/cloud/reference/sql/sql-functions/functions/AI_COMPLETE This section details the AI_COMPLETE function, its syntax, parameters, and provides an illustrative example of its usage in SQL. ```APIDOC ## AI_COMPLETE Function ### Description Specialized form of `AI_GENERATE` for creative text generation and summaries, returned as `VARCHAR`. ### Syntax ```sql AI_COMPLETE( [_model_name_ VARCHAR,] _prompt_ VARCHAR ) → VARCHAR ``` ### Parameters #### Path Parameters * None #### Query Parameters * None #### Request Body * None ### Function Arguments * **_model_name_** (VARCHAR) - Optional: Specifies the model to use in the format 'modelProvider.modelName' (e.g., 'gpt.4o'). If not provided, the organization's default model is used. * **_prompt_** (VARCHAR) - Required: The natural language instruction for the LLM, describing the desired output. ### Usage Notes * Optimized for creative text generation and summaries that return a single text response. * Always returns `VARCHAR` data type. * If no model is specified, uses the default model set for the organization. * Model specification format: 'modelProvider.modelName' (e.g., 'gpt.4o'). ### Request Example ```sql SELECT dish_name, AI_COMPLETE( 'Write an appetizing menu description for this dish: ' || dish_name || '. Main ingredients: ' || main_ingredients || '. Cooking style: ' || cuisine_type ) AS menu_description FROM restaurant_dishes; ``` ### Response #### Success Response (200) Returns a `VARCHAR` representing the generated text or summary. #### Response Example ```json { "dish_name": "Grilled Salmon", "menu_description": "Perfectly grilled Atlantic salmon with a delicate herb crust, served with seasonal vegetables and lemon butter sauce" } ``` ```json { "dish_name": "Beef Tacos", "menu_description": "Authentic street-style tacos featuring tender slow-cooked beef, fresh cilantro, and house-made salsa verde" } ``` ```json { "dish_name": "Chocolate Mousse", "menu_description": "Rich and velvety French chocolate mousse topped with fresh berries and a hint of vanilla" } ``` ``` -------------------------------- ### SQL: Get Sign of Numeric Expression (Double) Source: https://docs.dremio.com/cloud/reference/sql/sql-functions/functions/SIGN Returns 1 if the numeric expression is positive, -1 if negative, and 0 if zero. This specific example uses a double-precision floating-point number as input. ```sql SELECT SIGN(10.3) -- 1 ``` -------------------------------- ### Sample Flight SQL Client Usage Source: https://docs.dremio.com/cloud/sonar/custom-apps/arrow-flight-sql Instructions and command syntax for using the sample Flight SQL client application provided in the Apache Arrow repository, including connection details and command options. ```APIDOC ## Sample Flight SQL Client ### Description This section describes how to use the sample Flight SQL client application available in the Apache Arrow repository. It covers the command-line syntax for connecting to Dremio Cloud and executing various commands. ### Command Syntax ``` Usage: java -jar flight-sql-sample-client-application.jar -host localhost -port 443 ... -command,--command Method to run -dsv,--disableServerVerification Disable TLS server verification. Defaults to false. -host,--hostname `data.dremio.cloud` for Dremio's US control plane `data.eu.dremio.cloud` for Dremio's European control plane -kstpass,--keyStorePassword The jks keystore password. -kstpath,--keyStorePath Path to the jks keystore. -pat,--personalAccessToken Personal access token -port,--flightport 443 -query,--query The query to run -schema,--schema The schema to use -sp,--sessionProperty Key value pairs of SessionProperty, example: -sp schema='Samples."samples.dremio. com"' -sp key=value -table,--table The table to query -tls,--tls Enable encrypted connection. Defaults to true. ``` ### Example: Get Schemas This example demonstrates how to list schemas in Dremio Cloud using the sample client. **Request Command:** ```bash java -jar flight-sql-sample-client-application.jar -tls true -host data.dremio.cloud -port 443 --pat '' -command GetSchemas ``` **Example Output:** ``` catalog_name db_schema_name null @myUserName null INFORMATION_SCHEMA null Samples null sys ``` ``` -------------------------------- ### Get Day of Month from TIMESTAMP - Dremio SQL Source: https://docs.dremio.com/cloud/reference/sql/sql-functions/functions/DAYOFMONTH This example shows the usage of the DAYOFMONTH function with a TIMESTAMP expression in Dremio SQL. It accepts a TIMESTAMP literal and extracts the day of the month as a bigint. ```sql SELECT DAYOFMONTH(TIMESTAMP '2021-02-28 11:43:22') -- 28 ``` -------------------------------- ### Create View: Join Tables Source: https://docs.dremio.com/cloud/sonar/reflections_v=1 Example SQL query to join three tables (customers, orders, items) to create a view named 'order_detail'. This demonstrates data aggregation and preparation for further analysis. ```sql SELECT * FROM ((order INNER JOIN customer ON order.cust_id = customer.cust_id INNER JOIN item on order.item_id = item.item_id)) ``` -------------------------------- ### Get Current Date using CURRENT_DATE() in SQL Source: https://docs.dremio.com/cloud/reference/sql/sql-functions/functions/CURRENT_DATE This example demonstrates how to retrieve the current system date using the CURRENT_DATE() function in SQL. It is a standard SQL function that does not require external libraries. ```sql SELECT CURRENT_DATE() -- 2021-07-02 ``` -------------------------------- ### Retrieve Tags with Catalog API (curl) Source: https://docs.dremio.com/cloud/reference/api/catalog/tag Example of how to retrieve the tags associated with a specific dataset using the Catalog API. This is a GET request that requires the project ID and dataset ID. ```curl curl -X GET 'https://api.dremio.cloud/v0/projects/1df71752-69b7-47d9-9e6c-990e6b194aa4/catalog/1bcab7b3-ee82-44c1-abcc-e86d56078d4d/collaboration/tag' \ --header 'Authorization: Bearer ' \ --header 'Content-Type: application/json' ``` -------------------------------- ### Sample Arrow Flight Client Applications Source: https://docs.dremio.com/cloud/sonar/custom-apps/apps-with-arrow-flight Information on accessing sample Arrow Flight client applications provided by Dremio. ```APIDOC ## Sample Arrow Flight Client Applications Dremio provides sample Arrow Flight client applications in several languages at Dremio Hub. Both sample clients use the hostname `local` and the port number `32010` by default. Make sure to override these defaults with the hostname `data.dremio.cloud` or `data.eu.dremio.cloud` and the port number `443`. **Note:** The Python sample application only supports connecting to the default Sonar project in Dremio Cloud. ``` -------------------------------- ### Example: List Schemas using Flight SQL CommandGetDbSchemas Source: https://docs.dremio.com/cloud/sonar/custom-apps/arrow-flight-sql Demonstrates how to execute a `CommandGetDbSchemas` request using the sample Flight SQL client to list available schemas in a Dremio Cloud catalog. Requires a personal access token for authentication. ```bash java -jar flight-sql-sample-client-application.jar -tls true -host data.dremio.cloud -port 443 --pat '' -command GetSchemas ``` -------------------------------- ### SQL: Get Sign of Numeric Expression (Int64) Source: https://docs.dremio.com/cloud/reference/sql/sql-functions/functions/SIGN Returns 1 if the numeric expression is positive, -1 if negative, and 0 if zero. This example shows the function's behavior with a 64-bit integer input. ```sql SELECT SIGN(24) -- 1 ``` -------------------------------- ### Show View Definition SQL Command Source: https://docs.dremio.com/cloud/reference/sql/commands/show-create-view This SQL command retrieves the definition of a specific view, including its path and SQL definition. It requires the SELECT privilege on the view and supports specifying a reference (branch, tag, or commit) for the view definition. ```sql SHOW CREATE VIEW [ AT { REF[ERENCE] | BRANCH | TAG | COMMIT } ] ``` ```sql SHOW CREATE VIEW "company_data".Locations."offices_by_region" AT REF "myBranch" ``` -------------------------------- ### Get Day of Month from DATE - Dremio SQL Source: https://docs.dremio.com/cloud/reference/sql/sql-functions/functions/DAYOFMONTH This example demonstrates how to use the DAYOFMONTH function with a DATE expression in Dremio SQL. It takes a DATE literal as input and returns the day component as a bigint. ```sql SELECT DAYOFMONTH(DATE '2021-02-28') -- 28 ``` -------------------------------- ### Add Dataset Description (Markdown) for Dremio Source: https://docs.dremio.com/cloud/tutorials/zero-to-lakehouse/create-table This markdown snippet provides a descriptive overview of the 'nyc_trips' dataset, including its purpose, contact information, column definitions with data types and max values, and example SQL queries. It enhances data discoverability within Dremio. ```markdown This table contains taxi trips data from NYC. It can be used to determine the taxi trends i.e. frequency of trips, distance traveled, and average fares or tips. Questions? Contact gnarly@dremio.com ### Columns `pickup_datetime`: Time at which the passenger was picked up. `passenger_count`: The number of passengers that were picked up for the taxi ride. `trip_distance_mi`: The total distance of the trip from pickup to drop off locations in miles. * Max: 351.0 `fare_amount`: The dollar amount that the ride cost. * Max: 158995.81 `tip_amount`: The dollar amount that the customer tipped in addition to the fare_amount. * Max: 888.19 `total_amount`: The total dollar amount that the customer paid (fare + tip). ### Examples `SELECT DATE_PART('DAY', "catalog_name"."my_folder"."nyc_trips"."pickup_datetime") AS "pickup_day", SUM("catalog_name"."my_folder"."nyc_trips"."passenger_count") AS "passenger_count" FROM "catalog_name"."my_folder"."nyc_trips" GROUP BY pickup_day ORDER BY pickup_day;` ``` -------------------------------- ### Retrieve Reflection Summary (cURL) Source: https://docs.dremio.com/cloud/reference/api/reflection/reflection-summary This example demonstrates how to fetch reflection summaries from Dremio Cloud using a cURL command. It includes the necessary GET request, endpoint, and headers for authentication and content type. ```bash curl -X GET 'https://api.dremio.cloud/v0/api/projects/1df71752-69b7-47d9-9e6c-990e6b194aa4/reflection-summary' \ --header 'Authorization: Bearer ' \ --header "Content-Type: application/json" ``` -------------------------------- ### Get Character Length of a String with CHAR_LENGTH Source: https://docs.dremio.com/cloud/reference/sql/sql-functions/functions/CHAR_LENGTH This example demonstrates how to use the CHAR_LENGTH function to find the number of characters in a specific string. It takes a VARCHAR expression as input and returns an INTEGER representing the character count. ```sql SELECT CHAR_LENGTH('get the char length') -- 19 ``` ```sql SELECT CHAR_LENGTH('DREMIO') -- 6 ``` -------------------------------- ### Example dbt Profile Configuration for Dremio Cloud Source: https://docs.dremio.com/cloud/sonar/client-apps/dbt This is an example of a dbt profile configuration for connecting to Dremio Cloud. It includes essential parameters like the cloud host, project ID, authentication token, and storage details. Ensure sensitive information like 'pat' is handled securely. ```ini [project name]: outputs: dev: cloud_host: api.dremio.cloud cloud_project_id: 1ab23456-78c9-01d2-de3f-456g7h890ij1 object_storage_source: Samples object_storage_path: "samples.dremio.com"."NYC-taxi-trips" dremio_space: Folder1 dremio_space_folder: Folder2 pat: A1BCDrE2FwgH3IJkLM4NoPqrsT5uV6WXyza7I8bcDEFgJ9hIj0Kl1MNOPq2Rstu== threads: 1 type: dremio use_ssl: true user: name@company.com target: dev ``` -------------------------------- ### Switch to Main Branch in Dremio Arctic using Spark SQL Source: https://docs.dremio.com/cloud/tutorials/arctic-and-spark This command switches the current Spark SQL context to the 'main' branch within the 'arctic' catalog in Dremio. It's a prerequisite for verifying merges. ```sql spark.sql("USE REFERENCE main IN arctic") ``` -------------------------------- ### SQL Query Examples for Dremio Reflections Source: https://docs.dremio.com/cloud/sonar/reflections/best-practices These SQL snippets demonstrate how to define and create Dremio reflections. The first set shows example queries with similar join structures, and the second shows a generic query to create a raw reflection that can accelerate all three original queries. This is useful for optimizing join-heavy workloads. ```sql SELECT a.col1, b.col1, c.col1 FROM a JOIN b ON (a.col4 = b.col4) JOIN c ON (c.col5 = a.col5) WHERE a.size = 'M' AND a.col3 > '2001-01-01' AND b.col3 IN ( 'red', 'blue', 'green'); SELECT a.col1, a.col2, c.col1, COUNT(b1) FROM a JOIN b ON (a.col4 = b.col4) JOIN c ON (c.col5 = a.col5) WHERE a.size = 'M' AND b.col2 < 10 AND c.col2 > 2 GROUP BY a.col1, a.col2, c.col1; SELECT a.col1, b.col2 FROM a JOIN b ON (a.col4 = b.col4) JOIN c ON (c.col5 = a.col5) WHERE c.col1 = 123; ``` ```sql SELECT a.col1, a.col2, a.col3, b.col1, b.col2, b.col3, c.col1, c.col2 FROM a JOIN b ON (a.col4 = b.col4) join c ON (c.col5 = a.col5); ``` -------------------------------- ### Calculate Day Difference with TIMESTAMPDIFF Source: https://docs.dremio.com/cloud/reference/sql/sql-functions/functions/TIMESTAMPDIFF This example shows how to calculate the difference in days between two timestamps using the TIMESTAMPDIFF function. It takes the unit 'DAY', a starting timestamp, and an ending timestamp, returning the total number of days as an integer. ```sql SELECT TIMESTAMPDIFF(DAY, TIMESTAMP '2003-02-01 11:43:22', TIMESTAMP '2005-04-09 12:05:55'); -- 798 ``` -------------------------------- ### Dremio Oracle Source Configuration Example Source: https://docs.dremio.com/cloud/reference/api/catalog/source/source-config Example JSON configuration for connecting to an Oracle data source in Dremio. It includes authentication details, connection parameters, and Oracle-specific attributes like instance and SSL settings. ```json { "config": { "authenticationType": [ "ANONYMOUS", "MASTER" ], "username": "myUsername", "password": "myPassword", "hostname": "localhost", "port": "1521", "instance": "sales", "useSsl": true, "nativeEncryption": "ACCEPTED", "useTimezoneAsRegion": true, "includeSynonyms": false, "mapDateToTimestamp": true, "fetchSize": 200, "maxIdleConns": 8, "idleTimeSec": 60, "useLdap": false, "bindDN": "String", "sslServerCertDN": "String", "propertyList": [ { "name": "connect_timeout", "value": "0" } ] } } ```