測試環境為 Ubuntu 18.04 x86_64 (虛擬機 , IP: 192.168.111.143)
我要怎麼把資料庫的報表資料直接呈現在網頁上(表格或是圖表),可以透過 Redash (類似 Microsoft 的 Power BI ) 資料視覺化工具來做.
安裝 Mariadb (MySQL)
使用 MariaDB 當成我們的資料庫系統.
ben@ubuntu:~$ sudo su - [sudo] password for ben: root@ubuntu:~# apt-get -y install mariadb-server mariadb-client
Ubuntu 需要將 /etc/mysql/mariadb.conf.d/50-server.cnf 裡面的 bind-address = 127.0.0.1 註記起來 (#後面文字表示為說明),才能讓使用者遠端登入.
root@ubuntu:~# vi /etc/mysql/mariadb.conf.d/50-server.cnf #bind-address = 127.0.0.1
確定一下 MariaDB 執行中.
root@ubuntu:~# systemctl restart mysql
root@ubuntu:~# systemctl status mysql
● mariadb.service - MariaDB 10.1.41 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Thu 2019-10-03 02:35:37 PDT; 27s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Main PID: 11965 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 27 (limit: 4646)
CGroup: /system.slice/mariadb.service
└─11965 /usr/sbin/mysqld
資料庫初始化設定.
root@ubuntu:~# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n]
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n]
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n]
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n]
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
測試用資料
後續會用到資料庫的資料做範例,登入 MariaDB 並建立 testdb 資料庫.並建立以下的 tea 資料表 Table.
| Name | Item | Size | Note | Price |
|---|---|---|---|---|
| Ben1 | 粉圓綠茶 | M | 去冰無糖 | 30 |
| Ben2 | 粉圓鮮奶茶 | L | 微糖去冰 | 60 |
| Ben3 | 大甲芋頭鮮奶 | M | 熱,半糖 | 65 |
| Ben4 | 大甲芋頭鮮奶綠 | M | 微糖 | 65 |
| Ben5 | 溪口甘蔗青茶 | L | 微糖去冰甘蔗濃 | 68 |
| Ben6 | 鹿港麵茶 | L | 去冰微糖 | 65 |
| Ben7 | 溪口甘蔗清茶 + 粉圓 | L | 去冰微糖 | 73 |
| Ben8 | 溪口甘蔗清茶 + 粉圓 | L | 去冰微糖 | 73 |
| Ben9 | 溪口甘蔗牛奶 | L | 微糖去冰(牛奶無法做甘蔗濃) | 68 |
| Ben10 | 粉圓鮮奶茶 | L | 微糖去冰 | 60 |
| Ben11 | 大甲芋頭鮮奶 | M | 微糖 | 65 |
| Ben12 | 溪口甘蔗清茶 + 粉圓 | L | 去冰微糖 | 73 |
| Ben13 | 冬瓜檸檬露加愛玉 | L | 微糖常溫-加點九如檸檬青茶(去冰無糖55) | 70 |
| Ben14 | 溪口甘蔗牛奶 | L | 去冰無糖 | 68 |
| Ben15 | 宜蘭金桔綠 | L | 少冰無糖 | 55 |
| Ben16 | 中華愛玉檸檬 | L | 去冰微糖 | 55 |
| Ben17 | 溪口甘蔗牛奶 | L | 去冰半糖 | 68 |
| Ben18 | 溪口甘蔗牛奶 | L | 微冰半糖 | 68 |
| Ben19 | 大甲芋頭鮮奶 | M | 微糖 | 65 |
root@ubuntu:~# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 49 Server version: 10.1.41-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CREATE DATABASE testdb; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> USE testdb; Database changed
MariaDB [(none)]> CREATE TABLE tea (K1 INT(11) NOT NULL AUTO_INCREMENT , name VARCHAR(199) NOT NULL ,item VARCHAR(199) NOT NULL , size VARCHAR(5) NOT NULL , note VARCHAR(199) NOT NULL, price INT(11) NOT NULL, PRIMARY KEY (K1));
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> INSERT INTO tea(name , item , size , note , price) VALUES('Ben1','粉圓綠茶','M','去冰無糖','30') , ('Ben2','粉圓鮮奶茶','L','微糖去冰','60'), ('Ben3','大甲芋頭鮮奶','M','熱,半糖','65') , ('Ben4','大甲芋頭鮮奶綠','M','微糖','65'), ('Ben5','溪口甘蔗青茶','L','微糖去冰甘蔗濃','68') , ('Ben6','鹿港麵茶','L','去冰微糖','65') , ('Ben7','溪口甘蔗清茶 + 粉圓','L','去冰微糖','73') , ('Ben8','溪口甘蔗清茶 + 粉圓','L','去冰微糖','73') , ('Ben9','溪口甘蔗牛奶','L','微糖去冰(牛奶無法做甘蔗濃)','68') , ('Ben10','粉圓鮮奶茶','L','微糖去冰','60') , ('Ben11','大甲芋頭鮮奶','M','微糖','65') , ('Ben12','溪口甘蔗清茶 + 粉圓','L','去冰微糖','73') , ('Ben13','冬瓜檸檬露加愛玉','L','微糖常溫-加點九如檸檬青茶(去冰無糖55)','70') , ('Ben14','溪口甘蔗牛奶','L','去冰無糖','68') , ('Ben15','宜蘭金桔綠','L','少冰無糖','55') , ('Ben16','中華愛玉檸檬','L','去冰微糖','55') , ('Ben17','溪口甘蔗牛奶','L','去冰半糖','68') , ('Ben18','溪口甘蔗牛奶','L','微冰半糖','68') , ('Ben19','大甲芋頭鮮奶','M','微糖','65');
Query OK, 19 rows affected (0.00 sec)
Records: 19 Duplicates: 0 Warnings: 0
MariaDB [testdb]> SELECT * FROM tea; +----+-------+-----------------------------+------+--------------------------------------+-------+ | K1 | name | item | size | note | price | +----+-------+-----------------------------+------+--------------------------------------+-------+ | 1 | Ben1 | 粉圓綠茶 | M | 去冰無糖 | 30 | | 2 | Ben2 | 粉圓鮮奶茶 | L | 微糖去冰 | 60 | | 3 | Ben3 | 大甲芋頭鮮奶 | M | 熱,半糖 | 65 | | 4 | Ben4 | 大甲芋頭鮮奶綠 | M | 微糖 | 65 | | 5 | Ben5 | 溪口甘蔗青茶 | L | 微糖去冰甘蔗濃 | 68 | | 6 | Ben6 | 鹿港麵茶 | L | 去冰微糖 | 65 | | 7 | Ben7 | 溪口甘蔗清茶 + 粉圓 | L | 去冰微糖 | 73 | | 8 | Ben8 | 溪口甘蔗清茶 + 粉圓 | L | 去冰微糖 | 73 | | 9 | Ben9 | 溪口甘蔗牛奶 | L | 微糖去冰(牛奶無法做甘蔗濃) | 68 | | 10 | Ben10 | 粉圓鮮奶茶 | L | 微糖去冰 | 60 | | 11 | Ben11 | 大甲芋頭鮮奶 | M | 微糖 | 65 | | 12 | Ben12 | 溪口甘蔗清茶 + 粉圓 | L | 去冰微糖 | 73 | | 13 | Ben13 | 冬瓜檸檬露加愛玉 | L | 微糖常溫-加點九如檸檬青茶(去冰無糖55) | 70 | | 14 | Ben14 | 溪口甘蔗牛奶 | L | 去冰無糖 | 68 | | 15 | Ben15 | 宜蘭金桔綠 | L | 少冰無糖 | 55 | | 16 | Ben16 | 中華愛玉檸檬 | L | 去冰微糖 | 55 | | 17 | Ben17 | 溪口甘蔗牛奶 | L | 去冰半糖 | 68 | | 18 | Ben18 | 溪口甘蔗牛奶 | L | 微冰半糖 | 68 | | 19 | Ben19 | 大甲芋頭鮮奶 | M | 微糖 | 65 | +----+-------+-----------------------------+------+--------------------------------------+-------+ 19 rows in set (0.01 sec)
新增資料庫使用者
新增一個資料庫使用者 user1 可以從任何 (%) 地方連結資料庫,但只有 SELECT 權限.
MariaDB [(none)]> CREATE USER user1@'%' IDENTIFIED BY '111111'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> GRANT SELECT ON testdb.* TO user1@'%'; Query OK, 0 rows affected (0.00 sec) MariaDB [testdb]> exit; Bye
安裝 Redash
root@ubuntu:~# wget https://raw.githubusercontent.com/getredash/redash/master/setup/setup.sh --2019-10-03 00:35:57-- https://raw.githubusercontent.com/getredash/redash/master/setup/setup.sh Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 151.101.228.133 Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|151.101.228.133|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 2941 (2.9K) Saving to: ‘setup.sh’ setup.sh 100%[===========================================>] 2.87K --.-KB/s in 0s 2019-10-03 00:35:58 (29.4 MB/s) - ‘setup.sh’ saved [2941/2941] root@ubuntu:~# bash setup.sh ... Creating redash_redis_1 ... done Creating redash_postgres_1 ... done Creating redash_scheduler_1 ... done Creating redash_server_1 ... done Creating redash_scheduled_worker_1 ... done Creating redash_adhoc_worker_1 ... done
安裝時遇過 curl: (56) OpenSSL SSL_read: SSL_ERROR_SYSCALL, errno 104 問題,發現是網路連線問題,多試一次就可以了.
安裝已完成,使用瀏覽器 http://192.168.111.143/setup 設定帳號密碼即完成.
接下來透過三個步驟,把就資料轉換成為網頁上的表格或是圖表.
- Connect a Data Source – 連結你的資料來源.
前面設定好 MySQL 的資料庫與使用者.
Name : tea
Host : 192.168.111.143
User : user1
Password : 111111
Database name : testdbSave 儲存起來並透過 Test Connection 測試看看有沒有問題.
- Create your first Query – 產生 Query (資料要以哪種方式呈現轉,表格或是圖表).
資料來源為 MySQL 的 testdb 資料庫,透過以下的 SQL 語法 :SELECT item Item , size Size , note Note , count(*) COUNT , sum(price) Price FROM tea GROUP BY item,size;
並透過 New Visualization 把資料轉換成為網頁上的表格或是圖表 (更多圖表功能請參考 – https://ithelp.ithome.com.tw/users/20111638/ironman/1671 ).最後儲存 Save 並 Publish.
Chart Type 我用了兩種分別為 Bar 與 Pie - Create your first Dashboard – 將 Query 置入 Dashboard.
產生新的 Dashbard ,點選 Add Widget 把剛剛產生的 Query 加入並調整排版.
沒有解決問題,試試搜尋本站其他內容