Vertica学习.docx
《Vertica学习.docx》由会员分享,可在线阅读,更多相关《Vertica学习.docx(8页珍藏版)》请在冰豆网上搜索。
![Vertica学习.docx](https://file1.bdocx.com/fileroot1/2023-1/23/2e1ffa72-670d-40fc-af48-257569247613/2e1ffa72-670d-40fc-af48-2575692476131.gif)
Vertica学习
4.6Copy错误日志
copy执行错误后的Vertica的错误日志:
/database/dbname/dbname/v_dbname_node0002_catalog/CopyErrorLogs
4.7导数据(导出到数据文件)
从vertica数据的表中导出数据到数据文件,shell命令:
echo`vsql-ddbname-Udbadmin-Atq-wZongfen_12-c"select*fromtest.dim_flow_directionorderbyflow_type_code">/database/datastage/export/dim_all/test`
4.8导数据(数据表之间)
数据库表之间导数据
CONNECTTOVERTICAdbnameUSERdbadminPASSWORD'dbname'ON'192.168.1.1',5433;
exportTOVERTICAdbname.test.FCT_TNES_GN_NET_MFROMtest.FCT_TNES_GN_NET_M;
4.9加载数据
通过数据文件向vertica数据库里加载数据:
copytest.fct_flux_se_bus_res_ana_dfrom'/database/imp_file/fct_flux_se_bus_res_ana_d'onv_dbname_node0002delimiter'|';
4.10增加字段
(1)给表增加字段
altertabletest.DIM_DETAIL_SVCTYPEaddcolumnif_appnumeric(10,0);
4.11删除字段
(1)删除表字段
altertabletest.DIM_DETAIL_SVCTYPEdropcolumnif_app;
4.12修改操作
4.12.1修改字段为非空
altertabletest.fct_fournet_wlanap_equp_ana_daltercolumnday_idsetnotnull;
4.12.2更改字段数据类型
altertabletest.dim_micro_area_gsmaltercolumncell_idsetdatatypenumeric(15,0);
4.12.3修改普通表为分区表
altertabletest.fct_fournet_wlanap_equp_ana_dpartitionbyday_id;
4.12.4修改表名
altertabletest.fct_fournet_wlanap_equp_ana_d_xrenametofct_fournet_wlanap_equp_ana_d;
4.12.5修改表所属的用户
altertabletest.fct_fournet_wlanap_equp_ana_downertodev_test
对于数值类型:
types–INTEGER,INT,BIGINT,TINYINT,INT8,SMALLINT,andallNUMERICvaluesofscale<=18andprecision0之间是可以互相转化的。
此外,numeric类型的精度(precision)是无法更改的,但是长度(scale)是可以修改的,(0-18)之间可以互修改,(19-37)之间可以互修改。
4.13查询操作
4.13.1查询表
Tables
4.13.2Projections
projections
4.13.3查询列
columns
4.13.4查询注释
comments
4.13.5查询表对应的projection
SELECTowner_name,anchor_table_name,projection_nameFROMprojections WHEREprojection_basename='DIM_CFG_LEVEL';
4.13.6查询表的列对应的注释
SELECTt3.anchor_table_nameASTable_name,
SUBSTR(t1.object_name,INSTR(t1.object_name,'.',1)+1)ASColumn_name,
mentAScomment
FROMcommentst1,projectionst3
WHERE SUBSTR(t1.object_name,1,INSTR(t1.object_name,'.',1)-1)=
t3.projection_name
ANDt1.object_type='COLUMN'
ORDERBYt3.anchor_table_name;
4.14四舍五入
四舍五入、并且保留两位小数
SELECTTRIM(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.1RANDOM()
RANDOMhasnoarguments.ItsresultisaFLOAT8datatype
SELECTRANDOM();
random
-------------------
0.211625560652465
(1row)
4.15.2RANDOMINT
Returnsauniformly-distributedintegerI,where0<=I4.15.3RANDOMINT(N)
Example:
Inthefollowingexample,theresultisanINT8,whichis>=0andSELECTRANDOMINT(5);
randomint
----------
3
(1row)