没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
1.1
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
1.10
1.11
1.12
1.13
1.14
1.15
1.16
Table of Contents
简介
Vertica如何建表
SQL Server、Oracle、MySQL和Vertica数据库常用函数对比
Vertica中实现Oracle中的ws_concat功能
vertica加密数据
谈谈vertica的flex table
vertica中group by 和join 语句的优化
关于vertica的Connection Failover
Vertica常用管理
vertica存储统计信息
Vertica停止数据库的操作步骤
Vertica备份元数据信息
vertica备份与恢复
Vertica实时消费kafka实现
Vertica消费kafka答疑
Vertica报错TM
1
简介
Vertica是专为大数据分析构建的MPP架构、列式存储和计算的关系数据库;
Vertica基于x86无共享的MPP架构,没有共享资源,也没有主节点,无单点故障和瓶颈,很容易从
几个节点扩展到数百个节点,数据量从TBs扩展到10PBs级,除了可以部署在主流的x86物理机器
和云平台上外,还可以作为SQL on Hadoop部署在Hadoop平台上,提供简单易用、完整、高性能
的关系数据库。
Vertical具有以下特点:
1. 性价比高,支持大规模扩展,可部署于廉价的x86服务器/私有云/公有云/Hadoop,软件License
费用可预期,支持大规模节点扩展
2. 高处理性能,能够胜任大规模批量计算/高并发查询/极端复杂的自主分析和查询
3. 高频数据加载和实时分析,支持秒级数据实时加载和秒级甚至亚秒级的数据查询响应能力
4. 开放性和集成能力,可与Kafka/Hadoop Hive/HDFS/Spark无缝集成,可与R无缝集成,支持
各类BI产品和ETL工具
5. 具备企业级高可用和易管理特性,具备完善的部件失效和节点失效保护能力,和完善的全增量
备份机制
2
Vertica如何建表
大家看到题目可能会想,建表谁不会呀,还用讲嘛?但是如何建表能使你的应用查询更加快呢?
Anatomy of a Projection
The [CREATE PROJECTION]statement defines the individual elements of a projection, as
the following graphic shows.
The previous example contains the following significant elements:
Column List and Encoding
Lists every column in the projection and defines the encoding for each column. Unlike
traditional database architectures, HP Vertica operates on encoded data representations.
Therefore, HP recommends that you use data encoding because it results in less disk I/O.
Base Query
Identifies all the columns to incorporate in the projection through column name and table
name references. The base query for large table projections can contain PK/FK joins to
smaller tables.
Sort Order
The sort order optimizes for a specific query or commonalities in a class of queries based
on the query predicate. The best sort orders are determined by the WHERE clauses. For
example, if a projection's sort order is (x, y), and the query's WHERE clause specifies (x=1
AND y=2), all of the needed data is found together in the sort order, so the query runs
almost instantaneously. You can also optimize a query by matching the projection's sort
order to the query's GROUP BY clause. If you do not specify a sort order, HP Vertica uses
3
the order in which columns are specified in the column definition as the projection's sort
order. The ORDER BY clause specifies a projection's sort order, which localizes logically
grouped values so that a disk read can pick up many results at once. For maximum
performance, do not sort projections on LONG VARBINARY and LONG VARCHAR
columns.
Segmentation
The segmentation clause determines whether a projection is segmented across nodes
within the database. Segmentation distributes contiguous pieces of projections,
calledsegments, for large and medium tables across database nodes. Segmentation
maximizes database performance by distributing the load. Use SEGMENTED BY HASH to
segment large table projections. For small tables, use the UNSEGMENTED keyword to
direct HP Vertica to replicate these tables, rather than segment them. Replication creates
and stores identical copies of projections for small tables across all nodes in the cluster.
Replication ensures high availability and recovery. For maximum performance, do not
segment projections on LONG VARBINARY and LONG VARCHAR columns.
以上来自官网,理解如下:
Projection的解析
Sort Order
1、 order by 后表中插入的数据是有序的,所以order by 的列就源自于你在查询语句时使用的
where 字句的内容。例如,如果字句查询中有where x=1 and y=2,那么建立projection时order by
(x, y)查询的时候就会迅速定位到符合条件的数据
2、 group by 后面的字段,出现在order by 中也可以优化查询。
3、 order by 不要建立在LONG VARBINARY and LONG VARCHAR的列
Segmentation
1、 Segmentation by hash()就是按照某一列,打散数据,把数据均匀的分布在各个节点上,对
于大表,要记得使用。所以 hash里的列是主键最好,也就是说该列数据不重复的值越多,越适合做ha
sh.
2、 Segmentation by 的列不要用LONG VARBINARY and LONG VARCHAR columns.
`
根据以上规则建表,可以更大程度优化你的查询性能。
4
SQL Server、Oracle、MySQL和Vertica数据库常用函
数对比
SQL Server、Oracle、MySQL和Vertica数据库常用函数对比
1. 绝对值
S:select abs(-1) value
O:select abs(-1) value from dual
M:select abs(-1) value from dual
V:select abs(-1)
1. 向上取整
S:select ceiling(-1.001) value
O:select ceil(-1.001) value from dual
M:select ceil(-1.001) value from dual
V:select ceil(-1.001)
1. 向下取整
S:select floor(-1.001) value
O:select floor(-1.001) value from dual
M:select floor(-1.001) value from dual
V:select floor(-1.001)
1. 取整(截取)
S:select cast(-1.002 as int) value
O:select trunc(-1.002) value from dual
V:select trunc(-1.002)
5
剩余46页未读,继续阅读
资源评论
- qq_379218452023-10-23#毫无价值
- qq_417941822020-11-30正在学习vertica,棒棒哒
数据社
- 粉丝: 1551
- 资源: 6
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功