最近好几个同事都跟我聊到Vertica数据库，说到Vertica 拥有高性能、高扩展性、高压缩率、高健壮性的特点，很适合作为实时分析平台，自己也很期待去了解Vertica数据库；
Vertica是一款基于列存储的MPP（massively parallel processing）架构的数据库。 [2] 它可以支持存放多至PB（Petabyte）级别的结构化数据。Vertica是由关系数据库大师Michael Stonebraker(2014 年图灵奖获得者)所创建，于2011年被惠普收购并成为其核心大数据平台软件；
本篇文件记录我用时10分钟不到部署的Vertica数据库的过程，让大家看到体验Vertica数据库如此简单。
参考dockerhub网址：https://hub.docker.com/r/vertica/vertica-ce
我的Vertica数据库部署环境
操作系统版本
hhj@k-m:~$ cat /etc/issue
Ubuntu 18.04.5 LTS \n \l
hhj@k-m:~$ free -m
total used free shared buff/cache available
Mem: 11701 6104 4787 0 810 5352
Swap: 0 0 0
docker版本信息
Client:
Context: default
Debug Mode: false
Server:
ERROR: Got permission denied while trying to connect to the Docker daemon socket at unix:///var/run/docker.sock: Get ck: connect: permission denied
errors pretty printing info
hhj@k-m:~$ sudo docker info
[sudo] password for hhj:
Client:
Context: default
Debug Mode: false
Server:
Containers: 40
Running: 7
Paused: 0
Stopped: 33
Images: 18
Server Version: 20.10.7
Storage Driver: overlay2
Backing Filesystem: extfs
Supports d_type: true
Native Overlay Diff: true
userxattr: false
Logging Driver: json-file
Cgroup Driver: cgroupfs
Cgroup Version: 1
Plugins:
Volume: local
Network: bridge host ipvlan macvlan null overlay
Log: awslogs fluentd gcplogs gelf journald json-file local logentries splunk syslog
Swarm: inactive
Runtimes: io.containerd.runc.v2 io.containerd.runtime.v1.linux runc
Default Runtime: runc
Init Binary: docker-init
containerd version:
runc version:
init version:
Security Options:
apparmor
seccomp
Profile: default
Kernel Version: 4.15.0-140-generic
Operating System: Ubuntu 18.04.5 LTS
OSType: linux
Architecture: x86_64
CPUs: 1
Total Memory: 3.666GiB
Name: k-m
ID: 2QL5:WAWI:KZQU:BQF3:5TPQ:HYYZ:J3DX:HBKK:3O53:ACJI:ZGTB:M4QN
Docker Root Dir: /var/lib/docker
Debug Mode: false
Registry: https://index.docker.io/v1/
Labels:
Experimental: false
Insecure Registries:
127.0.0.0/8
Registry Mirrors:
https://b9pmyelo.mirror.aliyuncs.com/
Live Restore Enabled: false
下载dockerhub的vertica镜像
hhj@k-m:~$ sudo docker pull vertica/vertica-ce
Using default tag: latest
latest: Pulling from vertica/vertica-ce
2d473b07cdd5: Pull complete
0fc9b9ca08c6: Pull complete
33c6ce974df0: Pull complete
2b1cbae4fa6a: Pull complete
2230be515f10: Pull complete
f3015dc77be6: Pull complete
bffd05fd00b2: Pull complete
6f9a459b0b67: Pull complete
7461eb6348f2: Pull complete
4ee3f39058db: Pull complete
9aae71cf1ced: Pull complete
2a521e0d15be: Pull complete
Digest: sha256:b83464866fe47b9f5c1b761ff3b2972f26168af4f293995291806d20ac6a3137
Status: Downloaded newer image for vertica/vertica-ce:latest
docker.io/vertica/vertica-ce:latest
启动运行vertica容器过程
hhj@k-m:/$ sudo docker run -p 5433:5433 -p 5444:5444 \
> --mount type=volume,source=vertica-data,target=/data \
> --name vertica_ce \
> vertica/vertica-ce
VERTICA_DB_USER is "dbadmin"
Starting up
Moving config directory tree to persistent store
symlink /opt/vertica/config -> /data/vertica/config
Creating database
Info: no password specified, using none
Database with 1 or 2 nodes cannot be k-safe and it may lose data if it crashes
Distributing changes to cluster.
Creating database VMart
Starting bootstrap node v_vmart_node0001 (127.0.0.1)
Starting nodes:
v_vmart_node0001 (127.0.0.1)
Starting Vertica on all nodes. Please wait, databases with a large catalog may take a while to initialize.
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (UP)
Installing VFunctions package
Success: package VFunctions installed
Installing MachineLearning package
Success: package MachineLearning installed
Installing voltagesecure package
Success: package voltagesecure installed
Installing logsearch package
Success: package logsearch installed
Installing flextable package
Success: package flextable installed
Installing DelimitedExport package
Success: package DelimitedExport installed
Installing place package
Success: package place installed
Installing OrcExport package
Success: package OrcExport installed
Installing txtindex package
Success: package txtindex installed
Installing ComplexTypes package
Success: package ComplexTypes installed
Installing ParquetExport package
Success: package ParquetExport installed
Installing AWS package
Success: package AWS installed
Installing kafka package
Success: package kafka installed
Installing approximate package
Success: package approximate installed
Database creation SQL tasks completed successfully.
Database VMart created successfully.
Loading VMart schema ...
Dropping old schema ...
Timing is on.
Pager usage is off.
vsql:vmart_schema_drop.sql:1: ROLLBACK 4650: Schema "online_sales" does not exist
vsql:vmart_schema_drop.sql:2: ROLLBACK 4650: Schema "online_sales" does not exist
vsql:vmart_schema_drop.sql:3: ROLLBACK 4650: Schema "online_sales" does not exist
vsql:vmart_schema_drop.sql:4: ROLLBACK 4650: Schema "store" does not exist
vsql:vmart_schema_drop.sql:5: ROLLBACK 4650: Schema "store" does not exist
vsql:vmart_schema_drop.sql:6: ROLLBACK 4650: Schema "store" does not exist
vsql:vmart_schema_drop.sql:7: ROLLBACK 4876: Table "inventory_fact" does not exist
vsql:vmart_schema_drop.sql:8: ROLLBACK 4876: Table "customer_dimension" does not exist
vsql:vmart_schema_drop.sql:9: ROLLBACK 4876: Table "warehouse_dimension" does not exist
vsql:vmart_schema_drop.sql:10: ROLLBACK 4876: Table "shipping_dimension" does not exist
vsql:vmart_schema_drop.sql:11: ROLLBACK 4876: Table "employee_dimension" does not exist
vsql:vmart_schema_drop.sql:12: ROLLBACK 4876: Table "date_dimension" does not exist
vsql:vmart_schema_drop.sql:13: ROLLBACK 4876: Table "product_dimension" does not exist
vsql:vmart_schema_drop.sql:14: ROLLBACK 4876: Table "promotion_dimension" does not exist
vsql:vmart_schema_drop.sql:15: ROLLBACK 4876: Table "vendor_dimension" does not exist
vsql:vmart_schema_drop.sql:16: ROLLBACK 4650: Schema "store" does not exist
vsql:vmart_schema_drop.sql:17: ROLLBACK 4650: Schema "online_sales" does not exist
Generating data ...
datadirectory = ./
numfiles = 1
seed = 20177
null = ''
timefile = Time_custom.txt
numfactsalesrows = 5000000
numfactorderrows = 300000
numprodkeys = 500
numstorekeys = 50
numpromokeys = 100
numvendkeys = 50
numcustkeys = 50000
numempkeys = 10000
numwarehousekeys = 100
numshippingkeys = 100
numonlinepagekeys = 1000
numcallcenterkeys = 200
numfactonlinesalesrows = 5000000
numinventoryfactrows = 300000
gen_load_script = false
years = 2018 to 2022
Data Generated successfully !
Creating schema ...
Timing is on.
Pager usage is off.
CREATE SCHEMA
Time: First fetch (0 rows): 3.421 ms. All rows formatted: 3.755 ms
CREATE SCHEMA
Time: First fetch (0 rows): 392.153 ms. All rows formatted: 392.627 ms
CREATE TABLE
Time: First fetch (0 rows): 4.407 ms. All rows formatted: 4.792 ms
CREATE TABLE
Time: First fetch (0 rows): 4.489 ms. All rows formatted: 4.818 ms
CREATE TABLE
Time: First fetch (0 rows): 4.649 ms. All rows formatted: 4.980 ms
CREATE TABLE
Time: First fetch (0 rows): 4.931 ms. All rows formatted: 5.330 ms
CREATE TABLE
Time: First fetch (0 rows): 4.236 ms. All rows formatted: 4.671 ms
CREATE TABLE
Time: First fetch (0 rows): 4.848 ms. All rows formatted: 5.168 ms
CREATE TABLE
Time: First fetch (0 rows): 5.041 ms. All rows formatted: 5.486 ms
CREATE TABLE
Time: First fetch (0 rows): 4.300 ms. All rows formatted: 4.682 ms
CREATE TABLE
Time: First fetch (0 rows): 3.603 ms. All rows formatted: 4.004 ms
ALTER TABLE
Time: First fetch (0 rows): 5.054 ms. All rows formatted: 5.472 ms
CREATE TABLE
Time: First fetch (0 rows): 4.931 ms. All rows formatted: 5.308 ms
CREATE TABLE
Time: First fetch (0 rows): 3.532 ms. All rows formatted: 3.858 ms
ALTER TABLE
Time: First fetch (0 rows): 6.945 ms. All rows formatted: 7.292 ms
CREATE TABLE
Time: First fetch (0 rows): 4.281 ms. All rows formatted: 4.613 ms
ALTER TABLE
Time: First fetch (0 rows): 5.703 ms. All rows formatted: 6.072 ms
CREATE TABLE
Time: First fetch (0 rows): 5.051 ms. All rows formatted: 5.414 ms
CREATE TABLE
Time: First fetch (0 rows): 4.610 ms. All rows formatted: 5.018 ms
CREATE TABLE
Time: First fetch (0 rows): 4.165 ms. All rows formatted: 4.528 ms
ALTER TABLE
Time: First fetch (0 rows): 6.471 ms. All rows formatted: 6.914 ms
Loading files ...
Timing is on.
Pager usage is off.
Rows Loaded
-------------
1826
(1 row)
Time: First fetch (1 row): 48.188 ms. All rows formatted: 48.689 ms
Rows Loaded
-------------
500
(1 row)
Time: First fetch (1 row): 38.242 ms. All rows formatted: 38.649 ms
Rows Loaded
-------------
50
(1 row)
Time: First fetch (1 row): 38.125 ms. All rows formatted: 38.527 ms
Rows Loaded
-------------
100
(1 row)
Time: First fetch (1 row): 31.365 ms. All rows formatted: 31.770 ms
Rows Loaded
-------------
50
(1 row)
Time: First fetch (1 row): 33.412 ms. All rows formatted: 33.829 ms
Rows Loaded
-------------
50000
(1 row)
Time: First fetch (1 row): 292.815 ms. All rows formatted: 293.248 ms
Rows Loaded
-------------
10000
(1 row)
Time: First fetch (1 row): 79.851 ms. All rows formatted: 80.288 ms
Rows Loaded
-------------
100
(1 row)
Time: First fetch (1 row): 28.297 ms. All rows formatted: 28.694 ms
Rows Loaded
-------------
100
(1 row)
Time: First fetch (1 row): 27.611 ms. All rows formatted: 28.035 ms
Rows Loaded
-------------
1000
(1 row)
Time: First fetch (1 row): 31.773 ms. All rows formatted: 32.158 ms
Rows Loaded
-------------
200
(1 row)
Time: First fetch (1 row): 34.025 ms. All rows formatted: 34.475 ms
Rows Loaded
-------------
5000000
(1 row)
Time: First fetch (1 row): 17278.456 ms. All rows formatted: 17278.890 ms
Rows Loaded
-------------
300000
(1 row)
Time: First fetch (1 row): 1631.941 ms. All rows formatted: 1632.383 ms
Rows Loaded
-------------
5000000
(1 row)
Time: First fetch (1 row): 23741.498 ms. All rows formatted: 23741.936 ms
Rows Loaded
-------------
300000
(1 row)
Time: First fetch (1 row): 369.475 ms. All rows formatted: 369.848 ms
Running ETL ...
Timing is on.
Pager usage is off.
ALTER TABLE
Time: First fetch (0 rows): 2381.466 ms. All rows formatted: 2381.804 ms
ALTER TABLE
Time: First fetch (0 rows): 2192.468 ms. All rows formatted: 2192.854 ms
ALTER TABLE
Time: First fetch (0 rows): 166.233 ms. All rows formatted: 166.745 ms
ALTER TABLE
Time: First fetch (0 rows): 2230.525 ms. All rows formatted: 2230.871 ms
ALTER TABLE
Time: First fetch (0 rows): 396.229 ms. All rows formatted: 396.597 ms
OUTPUT
---------
5000000
(1 row)
Time: First fetch (1 row): 14033.652 ms. All rows formatted: 14041.379 ms
COMMIT
Time: First fetch (0 rows): 5.291 ms. All rows formatted: 5.303 ms
Confirm successful load
Timing is on.
Pager usage is off.
CREATE TABLE
Time: First fetch (0 rows): 6.985 ms. All rows formatted: 7.007 ms
Starting MC agent
Vertica is now running
查看启动的vertica数据库容器
hhj@k-m:~$ sudo docker ps
[sudo] password for hhj:
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
0b863e05016b vertica/vertica-ce "/bin/sh -c $ENTRYPO…" 3 minutes ago Up 3 minutes 0.0.0.0:5433->5433/tcp, :::5433->5433/tcp, 0.0.0.0:5444->5444/tcp, :::5444->5444/tcp vertica_ce
进入vertica数据库容器
hhj@k-m:/data$ sudo docker exec -it vertica_ce bash -l
[dbadmin@0b863e05016b ~]$
[dbadmin@0b863e05016b ~]$ vsql
Timing is on.
Pager usage is off.
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit
dbadmin@0b863e05016b()=>
第一次登陆vertica并查看有些功能。
查看帮助（和PG还有点相似）
dbadmin@0b863e05016b()=> \h
See https://www.vertica.com/documentation/vertica/11.0.x for information on available commands.
General
\c[onnect] [DBNAME|- [USER]]
connect to new database (currently "dbadmin")
\cd [DIR] change the current working directory
\q quit vsql
\set [NAME [VALUE]]
set internal variable, or list all if no parameters
\timing [on|off]
toggle timing of commands, or explicitly turn it on or off (currently on)
\unset NAME unset (delete) internal variable
\! [COMMAND] execute command in shell or start interactive shell
\password [USER]
change user's password
Query Buffer
\e [FILE] edit the query buffer (or file) with external editor
\g send query buffer to server
\g FILE send query buffer to server and results to file
\g | COMMAND send query buffer to server and pipe results to command
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w FILE write query buffer to file
Input/Output
\echo [STRING] write string to standard output
\i FILE execute commands from file
\o FILE send all query results to file
\o | COMMAND pipe all query results to command
\o close query-results file or pipe
\qecho [STRING]
write string to query output stream (see \o)
Informational
\d [PATTERN] describe tables (list tables if no argument is supplied)
PATTERN may include system schema name, e.g. v_catalog.*
\df [PATTERN] list functions
\dj [PATTERN] list projections
\dn [PATTERN] list schemas
\dp [PATTERN] list table access privileges
\ds [PATTERN] list sequences
\dS [PATTERN] list system tables. PATTERN may include system schema name
such as v_catalog, v_monitor, or v_internal.
Example: v_catalog.a*
\dt [PATTERN] list tables
\dtv [PATTERN] list tables and views
\dT [PATTERN] list data types
\du [PATTERN] list users
\dv [PATTERN] list views
\l list all databases
\z [PATTERN] list table access privileges (same as \dp)
Formatting
\a toggle between unaligned and aligned output mode
\b toggle beep on command completion
\C [STRING] set table title, or unset if none
\f [STRING] show or set field separator for unaligned query output
\H toggle HTML output mode (currently off)
\pset NAME [VALUE]
set table output option
(NAME := {format|border|expanded|fieldsep|footer|null|
recordsep|trailingrecordsep|tuples_only|title|tableattr|pager})
\t show only rows (currently off)
\T [STRING] set HTML <table> tag attributes, or unset if none
\x toggle expanded output (currently off)
查看默认有些什么库
dbadmin@0b863e05016b()=> \l
List of databases
name | user_name
-------+-----------
VMart | dbadmin
(1 row)
查看schemas
dbadmin@0b863e05016b(*)=> \dn
List of schemas
Name | Owner | Comment
--------------+---------+---------
v_internal | dbadmin |
v_catalog | dbadmin |
v_monitor | dbadmin |
public | dbadmin |
v_func | dbadmin |
v_txtindex | dbadmin |
store | dbadmin |
online_sales | dbadmin |
(8 rows)
查看默认有些什么用户
dbadmin@0b863e05016b(*)=> \du
List of users
User name | Is Superuser
-----------+--------------
dbadmin | t
(1 row)
创建我的一个vertica用户（操作和oracle、PG一样）
dbadmin@0b863e05016b()=> create user hhj identified by 'abcd@1234';
CREATE USER
Time: First fetch (0 rows): 10.472 ms. All rows formatted: 11.038 ms
dbadmin@0b863e05016b()=> \du
List of users
User name | Is Superuser
-----------+--------------
dbadmin | t
hhj | f
(2 rows)
完美收工，今天的分享就到这里，接下来好好亲身体验一下我的第一个vertica数据库。
最后修改时间：2021-12-31 17:23:07
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
