SQL-usage-notes.rst

SQL usage

(SQL flavours, Spark, Athena Presto, Postgres)

To get column type

Depends on the RDBMs

look for information_schema.columns table (if DB has it)

Presto/Athena

https://prestodb.io/docs/current/sql/show-columns.html

SHOW COLUMNS FROM table (doesn't work in Athena)

Get

select * from information_schema.columns where table_schema = '.....' and table_name = '.....'

select * from information_schema.columns

Oracle SQL

SELECT

DATA_TYPE

FROM

all_tab_columns

WHERE

table_name = 'TABLE NAME' -- in uppercase AND column_name = 'COLUMN NAME' -- in uppercase

Column name Example ================ ======= table_catalog awsdatacatalog table_schema some_schema table_name some_table column_name some_column ordinal_position 1, 2, 3.. column_default is_nullable "YES" data_type "varchar", "decimal(17,8)", (17,8) comment extra_info "partition_key"

Data Types Supported by Amazon Athena

https://docs.aws.amazon.com/athena/latest/ug/data-types.html

DDL vs DML

DDL Data Definition Language DML Data Manipulation Language

DDL defines structures like the schema, database, tables, constraints

eg. create, alter, drop, rename statements

DML

eg. insert, update, delete statements

DML can be futher classified into procedural and non-procedural DML

DCL data control language TCL transaction control language

SQL commands

DDL create alter drop truncate comment rename DML select insert update delete merge call 'explain plan' 'lock table' DCL grant revoke TCL commit rollback savepoint 'set transaction'

https://stackoverflow.com/questions/2578194/what-are-ddl-and-dml

Joins

• A "left outer join" is the same as a "left join". There is no such thing as a "left inner join" (because 'inner' join is rows from both tables.
• A 'join' by default means an inner join.
• An outer join is either 'left', 'right' or 'full'. Can't say just 'outer join'
• Can drop 'outer' and say, left join, right join, full join

Left joins and where clauses

A where clause with a left join will often filter away rows where the left join doesn't success. Move the where clause to the join.

ANSI-89, ANSI-92, when making outer joins, filtration location matters, since the criteria in the ON clause is applied before the JOIN is made.

So really a 'left outer join' (or left join) with where clauses, is then turned into an 'inner join'.

https://docs.aws.amazon.com/athena/latest/ug/functions-operators-reference-section.html

Athena DML query statements are based on Presto 0.172

The Athena query engine is based on HiveQL DDL.