10分钟快速部署一套vertica数据库

原创华军 2021-12-31

3924

最近好几个同事都跟我聊到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数据库。