目錄
前言
在我的上一篇墨天輪博客文章中描述了什么是Vertica數據庫，相信朋友們已經對Vertica數據庫有了一定的了解。那么，今天我將給大家帶來快速安裝Vertica數據庫和結合具體的業務場景來用Tableau可視化Vertica數據庫中的“DB Time”表。說到這里，也許您可能會問，什么是DB Time？因為我是一名Oracle DBA工程師，需要時刻關注Oracle的性能情況，而DB Time是衡量Oracle性能的一個非常重要的指標。為了能夠快速了解到Oracle數據庫最近一段時間內的DB Time，我們可以通過相關的性能查詢SQL語句來得到一段時間內的DB Time取值。但是在Oracle數據庫當中DB Time的保留時間是由AWR報告的保留策略決定的，根據實際的業務訴求，我們通常只保留一個月的AWR報告，所以DB Time的數據也只能保留一個月。為了能夠盡可能地將DB Time的數據保留的時間長一些，我們可以把DB Time的SQL查詢導出到CSV（各個值用逗號分開）文件，然后將這個CSV文件導入到Vertica數據庫（Vertica數據庫是一種列式存儲，支持大規模并行處理，非常適合海量數據的分析處理）然后用Tableau這個數據庫可視化工具連接到Vertica數據庫就能將DB Time表進行可視化，生成直觀形象的折線圖，讓我們能夠快速地了解什么時間段的DB Time值高，什么時間段的DB Time值會相對低一些，進而快速了解業務高峰期是什么時候和數據庫的總體運行情況。
在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=localhost3. 用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=> 創建連接用戶并授權
創建數據庫的連接用戶為“Qwz”并設置密碼，然后授予公共schema給該用戶。其具體的操作命令，如下所示：
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連接Vertica數據庫
用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（按每分鐘間隔）。
最后修改時間：2022-01-11 15:51:40
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
