Vertica-常用sql

外部表创建

CREATE EXTERNAL TABLE sales
(
    itemID INT,
    date   DATE,
    price  FLOAT
)
    AS COPY FROM 'hdfs:///dat/ext1.csv' DELIMITER ',';

# 本地文件
CREATE EXTERNAL TABLE teste (aa integer,bb integer) AS COPY FROM '/tmp/*.csv' DELIMITER ',';

创建分区表

CREATE TABLE public.store_orders
(
    order_no int,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date
)
    UNSEGMENTED ALL NODES  PARTITION BY YEAR(order_date);

修改分区

ALTER TABLE store_orders
    PARTITION BY EXTRACT(YEAR FROM order_date)*100 + EXTRACT(MONTH FROM order_date)
        GROUP BY EXTRACT(YEAR from order_date)*100 + EXTRACT(MONTH FROM order_date);

重组数据

ALTER TABLE store_orders REORGANIZE;

分区 分组 ，将特定分区合并到分组内

ALTER TABLE store_orders
    PARTITION BY order_date::DATE GROUP BY DATE_TRUNC('year', (order_date)::DATE) REORGANIZE;

查看分区

SELECT dump_table_partition_keys('store_orders');

分区统计

-- 表数量：
select count(distinct table_name)  FROM tables;
-- 分区表数量：
select count(distinct table_name) from PARTITION_COLUMNS;
-- 总表占大小：
SELECT sum(used_bytes)/1024/1024/1024 FROM projection_storage ;
-- 分区表总大小：
select sum(disk_space_bytes)/1024/1024/1024 from PARTITION_COLUMNS;
SELECT sum(used_bytes)/1024/1024/1024 FROM projection_storage where anchor_table_name in (select distinct table_name from PARTITION_COLUMNS);
-- 分区表大小（前10）：
select table_name,sum(disk_space_bytes)/1024/1024/1024 size from PARTITION_COLUMNS group by table_name order by size desc limit 10;
-- 分区表每个分区的大小（前20）：
select partition_key,sum(disk_space_bytes)/1024/1024/1024 size from PARTITION_COLUMNS group by partition_key order by size desc limit 20;

--查询每个表的分区数量
select tmp.count1 count,
       (select max(pc.table_name) from partition_columns pc where pc.projection_id = tmp.projection_id) name
from (select p.projection_id
           , count(1) count1
      from partitions p
      group by p.projection_id) tmp;

查看建表哦语句

select export_tables('','store_orders');