Vertica学习.docx

1、Vertica学习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

2、_all/test4.8 导数据（数据表之间）数据库表之间导数据 CONNECT TO VERTICA dbname USER dbadmin PASSWORD dbname ON 192.168.1.1,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

3、 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.1

4、2.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 修改表所属的用户alte

5、r 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 Projec

6、tionsprojections4.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)

7、+ 1) AS Column_name, ment AS comment FROM comments t1, projections t3WHERE SUBSTR (t1.object_name, 1, INSTR (t1.object_name, ., 1) - 1) = t3.projection_name AND t1.object_type = COLUMNORDER BY t3.anchor_table_name;4.14 四舍五入 四舍五入、并且保留两位小数SELECT TRIM (TO_CHAR (ROUND (3.456, 2.0), 999999999999999999.00

8、), 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 result is a FLOAT8 data typeSELECT RANDOM();random-0.211625560652465(1 row)4.15.2 RANDOMINTReturns a uniformly-distributed integer I, where 0 = I N, where N = 0 and N. In this case, INT8 is randomly chosen from the set 0,1,2,3,4.SELECT RANDOMINT(5);randomint-3(1 row)