Vertica常用SQL操作

一、查询与修改

1、查询

--导出表的结构：

select export_objects('','app_z.test')

--收集统计信息

select analyze_statistics('app_z.test')

--重置license_size

select audit_license_size()

--查看license

select get-compliance_status()

--查看表的大小

    select   anchor_table_schema,         
             anchor_table_name,         
             sum(used_bytes) / ( 1024^3 ) as used_compressed_gb  
     from    v_monitor.projection_storage  
    where    anchor_table_schema = 'app_z' 
group  by    anchor_table_schema,   anchor_table_name  
order  by    sum(used_bytes) desc;

2、授权及清表

--把一个schema上的权限赋给另一个用户

grant usage on schema dbname_dw to dev_test;

--把某个表的操作权限赋值给另一个用户

grant 权限名（all,select update,insert,delete） on 表名 to 用户名

--清表

truncate table tablename;

3、修改列名称

alter table app.test rename new_name to old_name;

4、查询字段出现的表名

select * from v_catalog.columns where table_name  like  upper('%mon%')

 

二、创表及插入导出

--登录

vsql -h 111.111.111.111 -d database -U user -w password -e -i -C ;

--导入数据

copy app_z.ofr_prom_part  from local 'c:/users/dell/desktop/type_in.csv' delimiter '分割符'  direct exceptions 'c:/users/dell/desktop/日志文件名.log' ;

--导出数据

vsql -h IP -d DATABASES -U USER -w PASSWORD -F "," -At -o C:\Users\dell\Desktop\a.csv -c "(SELECT * FROM   TABLE) ;"

或者：

@export on;
@export set Encoding="GBK" Filename="C:\Users\Administrator\Desktop\aa.csv" format="CSV" DecimalNumberFormat="00000000000" CsvColumnDelimiter=","CsvRowDelimiter="\r\n" CsvIncludeColumnHeader="true" ;
SELECT * FROM TABLE where bil_month = '201903';

创建临表

CREATE LOCAL TEMPORARY TABLE OFR_ASSET_PROM_N_HIST_${LocalCode} 
ON COMMIT PRESERVE ROWS 
AS ( SELECT *                 
    FROM TABLE
) ORDER BY ASSET_ROW_ID 
SEGMENTED BY HASH (ASSET_ROW_ID) ALL NODES KSAFE 0 ;

创建物理表

CREATE TABLE IF NOT EXISTS tablename
(         ID             INT,        
          NAME        VARCHAR(50),         
          DATE_CD    DATE,         
          STATURE    DECIMAL(18,2),         
          ACCS_NBR  CHAR(11)  
)  DIRECT -- 直接到物理层  分段存储  
ORDER BY  ID 
SEGMENTED BY HASH(ID) ALL NODES KSAFE 1;

 

三、函数

-- 周

select cast((timestamp '2005-01-17 10:00' - timestamp '2005-01-01') day as integer) / 7;

--求两点的时间间隔

Select 
  timestampdiff (256, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔年",
  timestampdiff (128, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔季度",
  timestampdiff (64, char(timestamp(current date) - timestamp('2017-11-14 15:24:23'))) AS "间隔月",
  timestampdiff (32, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔周",
  timestampdiff (16, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔日",
  timestampdiff (8, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔时",
  timestampdiff (4, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔分",
  timestampdiff (2, char(timestamp('2013-12-30 20:30:30') - timestamp('2001-09-26 15:24:23'))) AS "间隔秒"
FROM SYSIBM.SYSDUMMY1;

--查询字段在哪个表中出现

select * from v_catalog.columns where column_name = 'MKT_CHANNEL_LVL3_NAME';

--date_part对日期进行操作

select date_part('doy',sysdate)   -- 当天是当年的第几天, doy即 day of year  

select date_part('dow',sysdate) -- 当天是当周中的第几天, dow 即 day of week 

select date_part('month', sysdate) -- 当天的月份