Linux – Redbash

測試環境為 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

安裝 Redbash

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 設定帳號密碼即完成.

接下來透過三個步驟,把就資料轉換成為網頁上的表格或是圖表.

  1. Connect a Data Source – 連結你的資料來源.
    前面設定好 MySQL 的資料庫與使用者.
    Name : tea
    Host : 192.168.111.143
    User : user1
    Password : 111111
    Database name : testdb

    Save 儲存起來並透過 Test Connection 測試看看有沒有問題.

  2. 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

  3. Create your first Dashboard – 將 Query 置入 Dashboard.
    產生新的 Dashbard ,點選 Add Widget 把剛剛產生的 Query 加入並調整排版.
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料