Skip to content

Instantly share code, notes, and snippets.

@sqlparser
Created December 12, 2023 08:09
Show Gist options
  • Save sqlparser/eebff9c48618bec3896b0f0667d9fa8f to your computer and use it in GitHub Desktop.
Save sqlparser/eebff9c48618bec3896b0f0667d9fa8f to your computer and use it in GitHub Desktop.
Hive Sample SQL
```sql
SELECT pageid, adid
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;
CREATE DATABASE merge_data;
CREATE TABLE merge_data.transactions(
ID int,
TranValue string,
last_update_user string)
PARTITIONED BY (tran_date string)
CLUSTERED BY (ID) into 5 buckets
STORED AS ORC TBLPROPERTIES ('transactional'='true');
CREATE TABLE merge_data.merge_source(
ID int,
TranValue string,
tran_date string)
STORED AS ORC;
MERGE INTO merge_data.transactions AS T
USING merge_data.merge_source AS S
ON T.ID = S.ID and T.tran_date = S.tran_date
WHEN MATCHED AND (T.TranValue != S.TranValue AND S.TranValue IS NOT NULL) THEN UPDATE SET TranValue = S.TranValue, last_update_user = 'merge_update'
WHEN MATCHED AND S.TranValue IS NULL THEN DELETE
WHEN NOT MATCHED THEN INSERT VALUES (S.ID, S.TranValue, 'merge_insert', S.tran_date)
```
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment