sql
72 items tagged with "sql"
Articles
Snowflake - Flatten nested JSON array
Snowflake provides a number of JSON related functions to convert string (varchar) to JSON object and extract JSON values from the object or flatten nested array, etc. The code snippet shows an example of flattening the following JSON string using lateral flatten: ``json { "a":"a", "b":[ { "c":"c1", "d":[ 1, 2, 3, 4, 5, 6, 7 ] }, { "c":"c2", "d":[ 10, 20, 30, 40, 50, 60, 70 ] } ] } ``
Hive - Retrieve Current User
This code snippet provides example of retrieving current user via current_user() function in HQL (Hive QL) code. Output: `` 0: jdbc:hive2://> select currentuser();OK+----------+| c0 |+----------+| kontext |+----------+ ``
Hive SQL - Merge Statement on ACID Tables
Hive supports standard ANSI SQL MERGE statement from version 2.2. However it can be only be applied to tables that support ACID transactions. To learn more about ACID support in Hive, refer to article: Hive ACID Inserts, Updates and Deletes with ORC. Sample table This code snippet merges into a sample table named testdb.crudtable. It has two records before the merge. !20220819124209-image.png The staging table was created using the following statements: `` create table crudtablestg (id int, value string, op string); insert into crudtablestg values (1,'AA','U'),(2,'B','D'),(3,'C', 'I'); ` It has one additional column named op to indicate the delta changes: U - updates D - deletes I - inserts (i.e. new records) Syntax ` MERGE INTO AS T USING AS S ON WHEN MATCHED [AND ] THEN UPDATE SET WHEN MATCHED [AND ] THEN DELETE WHEN NOT MATCHED [AND ] THEN INSERT VALUES `` Output After the merge, record 1 is updated; record 2 is deleted and record 3 is inserted into the table.
Hive ACID Inserts, Updates and Deletes with ORC
Hive SQL - Union data with UNION ALL and UNION DISTINCT
Hive SQL - Analytics with GROUP BY and GROUPING SETS, Cubes, Rollups
Hive SQL - Data Sampling using TABLESAMPLE
Extract Values from XML Column in Hive Tables
Hive SQL - Cluster By and Distribute By
Hive SQL - Differences between Order By and Sort By
Hive SQL - Aggregate Functions Overview with Examples
List Tables in Hive Database
Create Partitioned Hive Table
PostgreSQL: Create and List User
BigQuery - Convert Bytes to BASE64 or HEX String
BigQuery MD5, SHA1, SHA256, SHA512 Hash Functions
Teradata: FIRST_VALUE and LAST_VALUE Funtions
Teradata: PIVOT and UNPIVOT Clause
Teradata Role Access on Databases
BigQuery - First/Last Day of Month
BigQuery SQL - WITH Clause
BigQuery SQL - SELECT * EXCEPT Clause
BigQuery SQL - Retrieve DISTINCT Values
BigQuery SQL - UNION ALL
BigQuery - Generate Unique Values
BigQuery SQL - COALESCE and IFNULL Functions
Teradata SQL - First Day of a Month
Teradata - Update with Joins to Another Table
Spark SQL - Convert String to Date
Calculate MD5 in Teradata
Create Procedure with Dynamic Return Result in Teradata
Convert TimeStamp to Date in Teradata
Teradata RANDOM Number
Create Multiset Temporary Table in Teradata
This article demonstrates how to create volatile table in a Teradata procedure, perform DML actions (INSERT, DELETE, UPDATE) against it and then return the result set dynamically from the temporary table in the procedure.
Teradata ISNULL Alternatives
Teradata RPAD - Right Padding
Teradata LPAD - Left Padding
Extract XML Data via SQL Functions in Teradata
Extract JSON Data via SQL Functions in Teradata
Select First Row in Each GROUP BY Group in Teradata
Create Temporary Table - Hive SQL
Create Table as SELECT - Hive SQL
Create Bucketed Sorted Table - Hive SQL
Create Partitioned Table - Hive SQL
Create Table Stored as CSV, TSV, JSON Format - Hive SQL
Create Table with Parquet, Orc, Avro - Hive SQL
Create, Drop, and Truncate Table - Hive SQL
Create, Drop, Alter and Use Database - Hive SQL
Teradata SQL - Round Numbers to Hundreds or Thousands
Teradata SQL - Get Rid of Spaces from String
Teradata SQL LIKE: Contains, Starts With, Ends With Functions
This page shows you how to use LIKE
Trim Leading or/and Trailing Zeros in Teradata
This page shows how to trim or remove leading or/and trailing zeros using Teradata SQL.
Truncate table in Teradata
Teradata SQL - Use OREPLACE to Replace or Remove Characters
Teradata SUBSTRING / SUBSTR and REGEXP_SUBSTR Functions
Teradata SQL - COALESCE and NULLIF Functions
Teradata SQL - TRYCAST / TRY_CAST Function
Teradata SQL - LEAD and LAG OLAP Functions
Teradata SQL - DATEADD Function Alternative for ADD / Subtract Days or Months
Teradata SQL - WITH - Use a reference of a CTE to another CTE
Read and parse JSON in SQL / Teradata
JSON is commonly used in modern applications for data storage and transfers. Pretty much all programming languages provide APIs to parse JSON.
Select top N records in SQL / Teradata
In different databases, the syntax of selecting top N records are slightly different. They may also differ from ISO standards.
Calculate time difference in SQL / Teradata
This code snippet shows how to calculate time differences.
Convert varchar to date in SQL / Teradata
This code snippet shows how to convert string to date in Teradata.
Select from dual in SQL / Hive
In Oracle database, you can select from dual table if you only want to return a one row result set. In many other databases, the query engine supports select directly from constant values without specifying a table name.
Select top N records in SQL / Hive
In different databases, the syntax of selecting top N records are slightly different. They may also differ from ISO standards.
Teradata SQL Tricks for SQL Server/Oracle Developers
For many SQL Server or Oracle developers, you may encounter some inconveniences when writing SQL queries. For example, how to select from dummy table or local defined variables. This page summarize the equivalents in Teradata SQL.