目錄

• 前言
• 在RHEL7上安裝單節點的Vertica數據庫
• 將Oracle數據庫的DB Time查詢導入Vertica數據庫
• 用Tableau可視化Vertica數據庫的表
• 參考內容

前言

[返回到頂部目錄]

在RHEL7上安裝單節點的Vertica數據庫

現在，我們在RHEL7操作系統上快速安裝一個單節點的Vertica數據庫。基本步驟是：

• 安裝CE（社區免費）版本的RPM包 vertica-11.0.1-2.x86_64.RHEL6.rpm
• 用install_vertica腳本配置Vertica數據庫集群
• 根據提示信息排錯并重新配置集群
• 在Vertica集群上創建一個新的數據庫
• 創建連接用戶并授權

安裝CE（社區免費）版本的RPM包 vertica-11.0.1-2.x86_64.RHEL6.rpm

將RPM包上傳到服務器并用命令進行安裝，如下所示：

[root@test ~]# ls -lrht vertica-11.0.1-2.x86_64.RHEL6.rpm 
-rw-r--r-- 1 root root 514M Dec  6 16:07 vertica-11.0.1-2.x86_64.RHEL6.rpm
[root@test ~]# rpm -ivh --nodeps vertica-11.0.1-2.x86_64.RHEL6.rpm 
warning: vertica-11.0.1-2.x86_64.RHEL6.rpm: Header V3 RSA/SHA256 Signature, key ID f54b82a0: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:vertica-11.0.1-2                 ################################# [100%]

Vertica Analytic Database v11.0.1-2 successfully installed on host test

To complete your NEW installation and configure the cluster, run: 
 /opt/vertica/sbin/install_vertica

To complete your Vertica UPGRADE, run:
 /opt/vertica/sbin/update_vertica

---------------------------------------------------------------------------------- 
Important
---------------------------------------------------------------------------------- 
Before upgrading Vertica, you must backup your database.  After you restart your   
database after upgrading, you cannot revert to a previous Vertica software version.
---------------------------------------------------------------------------------- 

View the latest Vertica documentation at https://www.vertica.com/documentation/vertica/

此時，可以看到/opt目錄下生成了vertica目錄，其大小為1.9G。

[root@test opt]# cd vertica/

[root@test vertica]# ls -lrht
total 3.5M
drwxrwxr-x  2 root root    6 Nov 20 08:04 log
-rw-r--r--  1 root root 3.4M Nov 20 08:11 LICENSES
drwxr-xr-x  2 root root   22 Dec 15 08:53 agent
drwxr-xr-x  2 root root 4.0K Dec 15 08:53 bin
drwxr-xr-x  4 root root  101 Dec 15 08:53 config
drwxr-xr-x  3 root root   26 Dec 15 08:53 examples
drwxr-xr-x  2 root root   57 Dec 15 08:53 en-US
drwxr-xr-x  2 root root   45 Dec 15 08:53 include
drwxr-xr-x  3 root root  101 Dec 15 08:53 java
drwxr-xr-x  2 root root 4.0K Dec 15 08:53 lib
drwxr-xr-x  2 root root   31 Dec 15 08:53 lib64
drwxrwxr-x  4 root root   36 Dec 15 08:53 oss
drwxr-xr-x 19 root root 4.0K Dec 15 08:54 packages
drwxr-xr-x  2 root root 4.0K Dec 15 08:54 sbin
drwxr-xr-x  2 root root 4.0K Dec 15 08:54 scripts
drwxrwxr-x  5 root root   91 Dec 15 08:54 sdk
drwxr-xr-x  9 root root  132 Dec 15 08:54 share
drwxrwxr-x  8 root root   80 Dec 15 08:54 spread
[root@test vertica]# du -sh .
1.9G    .

[返回到父目錄] [返回到頂部目錄]

用install_vertica腳本配置Vertica數據庫集群

接下來，我們用install_vertica腳本配置Vertica集群。詳見如下操作：

[root@test ~]# /opt/vertica/sbin/install_vertica --hosts 127.0.0.1 --rpm ~/vertica-11.0.1-2.x86_64.RHEL6.rpm --dba-user dbadmin
Vertica Analytic Database 11.0.1-2 Installation Tool


>> Validating options...


Mapping hostnames in --hosts (-s) to addresses...

>> Starting installation tasks.
>> Getting system information for cluster (this may take a while)...

Default shell on nodes:
127.0.0.1 /bin/bash

>> Validating software versions (rpm or deb)...

warning: /root/vertica-11.0.1-2.x86_64.RHEL6.rpm: Header V3 RSA/SHA256 Signature, key ID f54b82a0: NOKEY

>> Beginning new cluster creation...

successfully backed up admintools.conf on 127.0.0.1

>> Creating or validating DB Admin user/group...

Password for new dbadmin user (empty = disabled)  <<== 輸入 密碼 回車 (注意: 不回顯)
Successful on hosts (1): 127.0.0.1
    Provided DB Admin account details: user = dbadmin, group = verticadba, home = /home/dbadmin
    Creating group... Adding group
    Validating group... Okay
    Creating user... Adding user, Setting credentials
    Validating user... Okay


>> Validating node and cluster prerequisites...

Prerequisites not fully met during local (OS) configuration for
verify-127.0.0.1.xml:
    HINT (S0305): https://www.vertica.com/docs/11.0.x/HTML/index.htm#cshid=S0305
        HINT(eS0305): TZ is unset for dbadmin. Consider updating .profile or
        .bashrc
    WARN (N0010): https://www.vertica.com/docs/11.0.x/HTML/index.htm#cshid=N0010
        WARN(eN0010): Linux iptables (firewall) has some non-trivial rules in
        tables: filter, mangle
    WARN (S0112): https://www.vertica.com/docs/11.0.x/HTML/index.htm#cshid=S0112
        WARN(eS0112): vm.swappiness is higher than recommended: your 30 > 1
    FAIL (S0312): https://www.vertica.com/docs/11.0.x/HTML/index.htm#cshid=S0312
        FAIL(eS0312): Transparent hugepages is set to 'never'. Must be 'always'.

System prerequisites failed.  Threshold = WARN
        Hint: Fix above failures or use --failure-threshold

Installation FAILED with errors.

****
AdminTools and your existing Vertica databases may be unavailable.
Investigate the above warnings/errors and re-run installation.
****

[返回到父目錄] [返回到頂部目錄]

根據提示信息排錯并重新配置集群

從上面的安裝中，我們可以看到有四個重要的消息提示，它們依次為：HINT (S0305)、WARN (N0010)、WARN (S0112)和FAIL (S0312)，順便還告知了網址和采取的解決辦法。我們接著進行如下操作：

-- https://www.vertica.com/docs/11.0.x/HTML/Content/Authoring/InstallationGuide/BeforeYouInstall/TZenvironmentVar.htm?cshid=S0305

[root@test ~]# echo 'export TZ="Asia/Shanghai"' >> /home/dbadmin/.bash_profile

-- https://www.vertica.com/docs/11.0.x/HTML/Content/Authoring/InstallationGuide/BeforeYouInstall/CheckforSwappiness.htm?cshid=S0112

[root@test ~]# echo 0 > /proc/sys/vm/swappiness

[root@test ~]# echo 'vm.swappiness = 0' >> /etc/sysctl.conf

-- https://www.vertica.com/docs/11.0.x/HTML/Content/Authoring/InstallationGuide/BeforeYouInstall/transparenthugepages.htm?cshid=S0312

[root@test ~]# echo always > /sys/kernel/mm/transparent_hugepage/enabled

[root@test ~]# vi /etc/rc.local 
......
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
   echo always > /sys/kernel/mm/transparent_hugepage/enabled
fi
......
"/etc/rc.local" 34L, 1357C written

[root@test ~]# chmod +x /etc/rc.d/rc.local

經過上述操作以后，我們接著用install_vertica腳本進行配置，注意：最后加上參數和值“--failure-threshold FAIL”。因為WARN (N0010)的警告非常奇怪，而且我們已經關閉防火墻，Vertica官網上說，只能加這個參數來回避這個問題。

[root@test ~]# /opt/vertica/sbin/install_vertica --hosts 127.0.0.1 --rpm ~/vertica-11.0.1-2.x86_64.RHEL6.rpm --dba-user dbadmin --failure-threshold FAIL
Vertica Analytic Database 11.0.1-2 Installation Tool


>> Validating options...


Mapping hostnames in --hosts (-s) to addresses...

>> Starting installation tasks.
>> Getting system information for cluster (this may take a while)...

Default shell on nodes:
127.0.0.1 /bin/bash

>> Validating software versions (rpm or deb)...

warning: /root/vertica-11.0.1-2.x86_64.RHEL6.rpm: Header V3 RSA/SHA256 Signature, key ID f54b82a0: NOKEY

>> Beginning new cluster creation...

successfully backed up admintools.conf on 127.0.0.1 

>> Creating or validating DB Admin user/group...

Successful on hosts (1): 127.0.0.1
    Provided DB Admin account details: user = dbadmin, group = verticadba, home = /home/dbadmin
    Creating group... Group already exists
    Validating group... Okay
    Creating user... User already exists
    Validating user... Okay


>> Validating node and cluster prerequisites...

Prerequisites not fully met during local (OS) configuration for
verify-127.0.0.1.xml:
    WARN (N0010): https://www.vertica.com/docs/11.0.x/HTML/index.htm#cshid=N0010
        WARN(eN0010): Linux iptables (firewall) has some non-trivial rules in
        tables: filter, mangle

System prerequisites passed.  Threshold = FAIL


>> Establishing DB Admin SSH connectivity...

Installing/Repairing SSH keys for dbadmin


>> Setting up each node and modifying cluster...

Creating Vertica Data Directory...

Updating agent...
Creating node node0001 definition for host 127.0.0.1
... Done

>> Sending new cluster configuration to all nodes...

Starting or restarting agent...

>> Completing installation...

Running upgrade logic
Installation complete.

Please evaluate your hardware using Vertica's validation tools:
    https://www.vertica.com/docs/11.0.x/HTML/index.htm#cshid=VALSCRIPT

To create a database:
  1. Logout and login as dbadmin. (see note below)
  2. Run /opt/vertica/bin/adminTools as dbadmin
  3. Select Create Database from the Configuration Menu

  Note: Installation may have made configuration changes to dbadmin
  that do not take effect until the next session (logout and login).

To add or remove hosts, select Cluster Management from the Advanced Menu.

[返回到父目錄] [返回到頂部目錄]

在Vertica集群上創建一個新的數據庫

用Vertica官網上的11.0.x幫助文檔中的Creating a Database and Users來創建一個新的數據庫vdb_oracle_perf。具體操作如下所示：

1. 用su命令將從操作系統的root用戶切換到dbadmin用戶，查看集群狀態，它應該返回空值。注：因為返回行很冗長，所以在此省略了屏幕上提示的17條條款內容。

[root@test ~]# su - dbadmin
[dbadmin@test ~]$ 
[dbadmin@test ~]$ /opt/vertica/bin/admintools -t view_cluster
Micro Focus End User License Agreement - Enterprise Version

......


5200-0949 v1.0, 2017
? Copyright 2015-2017 EntIT Software LLC



Enter ACCEPT to accept license terms & conditions, or REJECT to not accept the license and quit: ACCEPT  <<== 輸入 ACCEPT
 DB | Host | State 
----+------+-------

2. 創建數據庫“vdb_oracle_perf”，并設置密碼為“oracle_perf”。

[dbadmin@test ~]$ /opt/vertica/bin/admintools -t create_db --data_path=/home/dbadmin --catalog_path=/home/dbadmin --database=vdb_oracle_perf --password=oracle_perf --hosts=localhost

3. 用vsql命令登錄到客戶端，密碼為前面設置的密碼，最終出現提示符“dbadmin=> ”。

[dbadmin@test ~]$ /opt/vertica/bin/vsql
Password:  <<== 輸入密碼(不回顯)
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=> 

[返回到父目錄] [返回到頂部目錄]

創建連接用戶并授權

dbadmin=> CREATE USER Qwz IDENTIFIED BY 'oracle';

dbadmin=> GRANT USAGE ON SCHEMA PUBLIC TO Qwz;

[返回到父目錄] [返回到頂部目錄]

將Oracle數據庫的DB Time查詢導入Vertica數據庫

• 將Oracle數據庫的DB Time的兩個SQL查詢分別保存為CSV文件
• 將兩個CSV文件上傳到服務器/home/dbadmin目錄下
• 用DBeaver 21.3.1客戶端工具連接到Vertica數據庫
• 在DBeaver 21.3.1上創建兩張表awr_dbtime和awr_dbtime_2
• 將兩個CSV文件分別導入剛創建的兩張表中

將Oracle數據庫的DB Time的兩個SQL查詢分別保存為CSV文件

用Oracle SQL Developer將下面的兩個DB Time的SQL查詢以腳本方式運行并保存為CSV文件，詳見具體的SQL代碼和對應的屏幕截圖。

SET FEEDBACK  off;
SET SQLFORMAT csv;

-- DB time in Last 31 Days (interval by each hour).

SET LINESIZE 200
SET PAGESIZE 200

COLUMN snap_date_time FORMAT a19
COLUMN stat_name      FORMAT a10
COLUMN dbtime         FORMAT 999,999.99

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

WITH st AS
(
  SELECT snap_id
       , dbid
       , instance_number
       , end_interval_time
  FROM dba_hist_snapshot
),
stm AS
(
  SELECT snap_id
       , dbid
       , instance_number
       , stat_name
       , value
  FROM dba_hist_sys_time_model
  WHERE stat_name = 'DB time'
),
dbtime_per_hour AS
(
  SELECT CAST(st.end_interval_time AS DATE) snap_date_time
       , stm.stat_name
       , ROUND((stm.value - LAG(stm.value, 1, 0) OVER (PARTITION BY stm.dbid, stm.instance_number ORDER BY stm.snap_id))/1e6/6e1, 2) dbtime
  FROM st
     , stm
  WHERE st.snap_id = stm.snap_id
  AND   st.instance_number = stm.instance_number
  AND   st.dbid = stm.dbid
  AND   CAST(st.end_interval_time AS DATE) >= SYSDATE - 31
  ORDER BY snap_date_time
)
SELECT snap_date_time  -- the group column
     , stat_name       -- the series column
     , dbtime          -- the value column
FROM dbtime_per_hour
WHERE dbtime NOT IN (SELECT MAX(dbtime) FROM dbtime_per_hour)
;

SET FEEDBACK  off;
SET SQLFORMAT csv;

-- DB time in Last 1 Hour (interval by each minute).

SET LINESIZE 200
SET PAGESIZE 200

COLUMN snap_date_time FORMAT a19
COLUMN stat_name      FORMAT a10
COLUMN dbtime         FORMAT 999,999.99

SELECT end_time    snap_date_time
     , DECODE(metric_name, 'Average Active Sessions', 'AAS') stat_name
     , ROUND(value, 2)*60 dbtime
-- FROM dba_hist_sysmetric_history
FROM v$sysmetric_history
WHERE metric_name = 'Average Active Sessions'
AND   group_id = 2
AND   end_time >= SYSDATE - INTERVAL '60' MINUTE
ORDER BY snap_date_time
;

[返回到父目錄] [返回到頂部目錄]

將兩個CSV文件上傳到服務器/home/dbadmin目錄下

[dbadmin@test ~]$ ls -lrht
total 40K
drwxr-xr-x 5 dbadmin verticadba  134 Dec 15 14:06 vdb_oracle_perf
-rw-r--r-- 1 dbadmin verticadba  27K Dec 16 18:37 oracle_dbtime.csv    <<==
-rw-r--r-- 1 dbadmin verticadba 1.9K Dec 16 18:37 oracle_dbtime_2.csv  <<==

[返回到父目錄] [返回到頂部目錄]

用DBeaver 21.3.1客戶端工具連接到Vertica數據庫

[返回到父目錄] [返回到頂部目錄]

在DBeaver 21.3.1上創建兩張表awr_dbtime和awr_dbtime_2

創建步驟分別詳見如下兩圖：

[返回到父目錄] [返回到頂部目錄]

將兩個CSV文件分別導入剛創建的兩張表中

相應的屏幕截圖如下所示：

[返回到父目錄] [返回到頂部目錄]

用Tableau可視化Vertica數據庫的表

• 用Tableau連接Vertica數據庫
• 用Tableau可視化表awr_dbtime_2（最近1小時，按每分鐘間隔）
• 用Tableau可視化表awr_dbtime（最近31天，按每小時間隔）
• 用Tableau可視化表awr_dbtime（最近31天，按每天間隔）

[返回到父目錄] [返回到頂部目錄]

用Tableau可視化表awr_dbtime_2（最近1小時，按每分鐘間隔）

在連接成功Vertica數據庫以后，首先選擇架構（這里翻譯得可能有誤，應該叫schema）public，然后將表awr_dbtime_2拖動到指定的位置，單擊底部的“工作表1”，順次見如下兩圖：

接著，我們按照簡單的五個步驟來進行可視化表awr_dbtime_2，這個表里包含按每分鐘間隔的最近1小時的DB Time取值。每個步驟的屏幕截圖依次如下所示：

最后，我們用鼠標移動到一個對應的標簽上面，它會顯示具體的快照時間和DB Time取值，效果如下圖：

至此，最近1小時的DB Time（按每分鐘間隔）的折線圖已經制作完成。另外，Tableau可以將其導出并保存為PDF或PowerPoint格式的文件。

[返回到父目錄] [返回到頂部目錄]

用Tableau可視化表awr_dbtime（最近31天，按每小時間隔）

和前面的可視化表awr_dbtime_2的方法相同，在此省略一些設置步驟，直接附上最終的折線圖。

[返回到父目錄] [返回到頂部目錄]

用Tableau可視化表awr_dbtime（最近31天，按每天間隔）

同樣的思路和方法，最終的效果圖如下所示：

以上就是這篇博客文章的全部內容，歡迎朋友們在評論區提出您的寶貴意見或建議，我將認真閱您的反饋信息，并盡快逐一回復，謝謝！

[返回到父目錄] [返回到頂部目錄]

參考內容

• 什么是Vertica數據庫？
  
• Quickstart Guide from the official documentation of Vertica 11.0.x
  
• HINT (S0305)
  
• WARN (N0010)
  
• WARN (S0112)
  
• FAIL (S0312)
  
• DBeaver Community Edition 21.3.1
  
• Using This Guide from the official documentation of Vertica 11.0.x
  
• Tableau Desktop 2021.4.1
  
• Oracle導入csv文本文件到Vertica
  
• Visual output in Vertica
  
• Tableau生成折線圖
  

更新于 2021年12月18日 晚上：

您也可以在我的GitHub上找到如何查詢Oracle數據庫最近31天的DB Time（按每小時間隔）和最近1小時的DB Time（按每分鐘間隔）。