Vertica学习.doc

Vertica学习一、简介Vertica是一款基于列存储的MPP（massively parallel processing）架构的数据库。它可以支持存放多至PB（Petabyte）级别的结构化数据。Vertica是由关系数据库大师Michael Stonebraker(2014 年图灵奖获得者)所创建，于2011年被惠普收购并成为其核心大数据平台软件。二、优点基于列存储的数据库，相对于传统的基于行的数据库，它更适合在数据仓库存储方面发挥特长。基于列存储的数据库的优点：a)、对于聚集操作，比如求sum，明显基于列存储的要比基于行存储的快；b)、对于update操作，不须接触其他列值；c)、基于行存储的数据库在查询每行记录的多个列值更高效的条件是， row-size比较小，这样一次磁盘读取就可以获取整行d)、基于行存储的数据库在insert一行的时候相对更高效，毕竟可一次写入一个连续空间，即一次single disk seek。 从实际情况上来看，基于行存储的数据库更适合OLTP（联机事务处理系统），基于列存储的数据库更适合OLAP（联机分析处理系统），比如数据仓库。除此之外，同一列必定是同一类型大小，基于列存储的数据库更容易使用高效的存储方式，与之相对，基于行存储的数据库则只能采用随机方式处理列值了。三、特点a)、它是基于列的存储结构，提高了连续的record处理的性能，但是在一般事务中增加了对单独record进行update和delete的开销b)、“单独”更新（out-of-place updates）和混合存储结构，提高了查询、插入的性能，但增加了update和delete的开销；c)、压缩，减少存储开销和IO带宽开销；d)、完全无共享架构，降低对共享资源的系统竞争。Vertica数据库运行在基于Linux的网格服务器上，目前应用于Amazon Elastic Compute Cloud的数据库管理系统。四、常用命令4.1 进入vertica的sql环境 vsql -d dbname -w password在vertica的sql环境下获取帮助 dbname=h（1）查询用户：select * from v_catalog.users;（2）查询schema：select * from schemata;注：vertica中，某个schema必须附属于某个用户（user），查询用户和schema信息SELECT u.user_name, s.schema_name FROM users u LEFT OUTER JOIN schemata s ON u.user_name = s.schema_owner4.2 创建用户和schema（1）创建一个用户：create user dev_test identified by test;（2）基于某个用户创建schema：create schema if not exists test authorization dev_test;（3）重命名（备份用）dataname数据库为 dataname_bak：alter schema dataname rename to dataname_bak;（4）删除dataname库：drop schema dataname cascade;4.3 赋权（1）一个schema上的权限赋给另一个用户：GRANT USAGE ON SCHEMA dbname_dw TO dev_test;（2）把对某个表的操作的权限赋给另一个用户：GRANT ALL ON TABLE tw_re_pm_cell_all_cell_h to dev_test;（3）从某个用户收回对某个schema的使用权限：revoke all on SCHEMA dbname_dw from dev_test;（4）从某个用户收回对某个表的使用权限：revoke all on table fct_flux_se_flux_flow_whole_ana_d from dev_test;4.4 序列4.4.1查询系统中的序列select * from sequences;4.4.2创建序列 简单语法：CREATE SEQUENCE sq1 MAXVALUE 5000 START 1; 标准语法：CREATE SEQUENCE db-name.schema.sequence_name . INCREMENT BY positive_or_negative . MINVALUE minvalue | NO MINVALUE . MAXVALUE maxvalue | NO MAXVALUE . START WITH start . CACHE cache . CYCLE | NO CYCLE 4.4.3使用序列1、SELECT NEXTVAL(my_seq);SELECT CURRVAL(my_seq);注：一个新创建还没有使用过的序列，必须首先执行NEXTVAL，然后才能执行CURRVAL。2、在INSERT语句里使用序列：INSERT INTO customer VALUES (Hawkins ,John, 072753, NEXTVAL(my_seq);3、在INSERT语句里把序列作为默认值：CREATE TABLE customer2(ID INTEGER DEFAULT NEXTVAL(my_seq), lname VARCHAR(25), fname VARCHAR(25), membership_card INTEGER ); = INSERT INTO customer2 VALUES (default,Carr, Mary, 87432);4.4.4 删除序列DROP SEQUENCE seq_name;4.5 建表（外部表）CREATE EXTERNAL TABLE ext1 (x integer) AS COPY FROM /tmp/ext1.dat DELIMITER ,; CREATE EXTERNAL TABLE ext1 (x integer) AS COPY FROM /tmp/ext1.dat.bz2 BZIP DELIMITER ,; CREATE EXTERNAL TABLE ext1 (x integer, y integer) AS COPY (x as 5, y) FROM /tmp/ext1.dat.bz2 BZIP DELIMITER ,;4.6 Copy错误日志copy执行错误后的Vertica的错误日志：/database/dbname/dbname/v_dbname_node0002_catalog/CopyErrorLogs4.7 导数据（导出到数据文件）从vertica数据的表中导出数据到数据文件，shell命令： echo vsql -d dbname -U dbadmin -Atq -w Zongfen_12 -c select * from test.dim_flow_direction order by flow_type_code /database/datastage/export/dim_all/test4.8 导数据（数据表之间）数据库表之间导数据 CONNECT TO VERTICA dbname USER dbadmin PASSWORD dbname ON ,5433; export TO VERTICA dbname.test.FCT_TNES_GN_NET_M FROM test.FCT_TNES_GN_NET_M;4.9 加载数据通过数据文件向vertica数据库里加载数据：copy test.fct_flux_se_bus_res_ana_d from /database/imp_file/fct_flux_se_bus_res_ana_d on v_dbname_node0002 delimiter |;4.10 增加字段（1）给表增加字段 alter table test.DIM_DETAIL_SVCTYPE add column if_app numeric(10,0);4.11 删除字段（1）删除表字段 alter table test.DIM_DETAIL_SVCTYPE drop column if_app;4.12 修改操作4.12.1 修改字段为非空alter table test.fct_fournet_wlanap_equp_ana_d alter column day_id set not null;4.12.2 更改字段数据类型alter table test.dim_micro_area_gsm alter column cell_id set data type numeric(15,0);4.12.3 修改普通表为分区表alter table test.fct_fournet_wlanap_equp_ana_d partition by day_id;4.12.4 修改表名alter table test.fct_fournet_wlanap_equp_ana_d_x rename to fct_fournet_wlanap_equp_ana_d;4.12.5 修改表所属的用户alter table test.fct_fournet_wlanap_equp_ana_d owner to dev_test对于数值类型：typesINTEGER, INT, BIGINT, TINYINT, INT8, SMALLINT, and all NUMERIC values of scale =18 and precision 0 之间是可以互相转化的。此外，numeric类型的精度（precision）是无法更改的，但是长度(scale)是可以修改的，（0-18）之间可以互修改，（19-37）之间可以互修改。4.13 查询操作4.13.1 查询表Tables4.13.2 Projectionsprojections4.13.3 查询列columns4.13.4 查询注释comments4.13.5 查询表对应的projectionSELECT owner_name, anchor_table_name, projection_name FROM projectionsWHERE projection_basename = DIM_CFG_LEVEL;4.13.6 查询表的列对应的注释SELECT t3.anchor_table_name AS Table_name, SUBSTR (t1.object_name, INSTR (t1.object_name, ., 1) + 1) AS Column_name, ment AS comment FROM comments t1, projections t3WHERE SUBSTR (t1.object_name, 1, INSTR (t1.object_name, ., 1) - 1) = jection_name AND t1.object_type = COLUMNORDER BY t3.anchor_table_name;4.14 四舍五入 四舍五入、并且保留两位小数SELECT TRIM (TO_CHAR (ROUND (3.456, 2.0), 999999999999999999.00), TRIM (TO_CHAR (ROUND (3, 2.0), 999999999999999999.00), TRIM (TO_CHAR (ROUND (3.00, 2.0), 999999999999999999.00), TRIM (TO_CHAR (ROUND (323542.101, 2.0), 999999999999999999.00), TRIM (TO_CHAR (ROUND (3.1067, 2.0), 999999999999999999.00)4.15 产生随机数4.15.1 RANDOM()RANDOM has no arguments. Its