SQL (8) 썸네일형 리스트형 [impala / oracle / presto/spark] Confusing Date SQL Statement Differences 1. impala tx_dt between from_timestamp(date_add(to_timestamp(t1.base_dt, 'yyyyMMdd'),-3),'yyyyMMdd') and t1.base_dt2 a.nbr = cat(b.nbr as string) select substr(to_date(months_add(now(),-1)),1,7) as base_ym base_dt = to_date(now()) base_dt = to_date(date_add(now(),-1)) to_timestamp(tx_tmstmp,'yyyyMMddHHmmss') > date_add(now(), interval -10 minutes) count(case when t1.base_dt = from_timestamp(last.. [kudu] query(insert/upsert/update/delete), coding(python, pyspark) [query] INSERT INTO my_first_table VALUES (1, "john"), (2, "jane"), (3, "jim"); UPSERT INTO my_first_table VALUES (99, "zoe"); UPDATE my_first_table SET name="bob" where age > 10; DELETE FROM my_first_table WHERE id < 3; [python] import kudu client = kudu.Client("myhost.com:port") tableName = "impala::tmp.table_name" kuduTable = client.table(tableName) session = client.new_session() op = kuduTab.. [hive] partition add / delete ALTER TABLE tmp.table ADD IF NOT EXISTS PARTITION (base_dt='2020-06-01') ALTER TABLE tmp.tableDROP IF EXISTS PARTITION(year = 2012, month = 12, day = 18); [hive] table, column add comment / add column / change column order / column rename table comment add ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment); column comment add ALTER TABLE table_name CHANGE col1 col1 colType COMMENT 'new_comment'; 파티션 컬럼은 테이블생성시에만 코멘트를 추가할 수 있다. Comment can be added to a partition column at the time of creation of the table. HUE > Table Browser > edit 가능 change column order ALTER TABLE tmp.table_name CHANGE col_name col_name col_typ.. [hive] partition column rename hive에서 실행할것. impala에서 하면 실행안됨 ALTER TABLE tmp.table PARTITION (base_dt='20200601') RENAME TO PARTITION (base_dt='2020-06-01') hadoop distcp /old_dir/base_dt=20200601/* /new_dir/base_dt=2020-06-01/filename 파티션 컬럼 경로 변경 ALTER TABLE tmp.table PARTITION (base_dt='2020-06-01') SET LOCATION '/newdir/base_dt=2020-06-01' [hive] table rename , change location 테이블명 변경 alter table tmp.oldname rename to tmp.newname 경로 변경 터미널에서 파일 복사 hadoop distcp hdfs://old_dir/* hdfs://new_dir 쿼리 alter table tmp.newname set location 'hdfs://new_dir' [kudu] column add/change 칼럼명 변경 alter table table_name change old_col new_col new_type 칼럼 추가 alter table table_name add columns (col1 type1, col2 type2) [kudu] create table CREATE TABLE my_first_table ( id BIGINT, name STRING, PRIMARY KEY(id) ) PARTITION BY HASH PARTITIONS 6 STORED AS KUDU TBLPROPERTIES ('kudu.master_addresses' = 'host_name') 이전 1 다음