Category: Database

  • Ambari #07 เปรียบเทียบความเร็วของการ Query ระหว่าง MySQL กับ Hive

    จากบทความก่อนหน้า

    Ambari #05 การดึงข้อมูลเข้าจาก MySQL เข้าสู่ Hive ด้วย Sqoop ได้นำเข้าข้อมูล Mail Log จาก MySQL ซึ่งมีขนาด 27 GB มีข้อมูลประมาณ 12 ล้าน Record

    ต่อไปจะเปรียบเทียบ การ Query ข้อมูลจาก  Hive ซึ่งทำงานอยู่บน Hadoop Cluster กับ MySQL Server

    Spec

    MySQL Server: 1 Node x CPU 40 Core x RAM 8 GB

    Hive: 7 Data Node x CPU 4 Core x RAM 4 GB

    Query:

    use mailscanner;
select from_domain,count(*) from maillog group by from_domain having from_domain like '%.com';

    ซึ่ง from_domain เป็น Field ที่ไม่มีการทำ index

    ผลการทดสอบ

    MySQL: ใช้เวลา 5 นาที 23.90 วินาที = 329.90 วินาที

     

    Hive: ใช้เวลา 92.754 วินาที


    อนึ่ง : Hive ที่ใช้งานนั้น ทำงานบน  Execution Engine ชื่อ Tez ซึ่งทำงานทั้งหมดบน Memory หากใช้งาน Hive ที่มากับ Apache โดยตรง จะใช้งาน MapReduce ซึ่งจะใช้เวลานานกว่ามาก (ยังไม่ได้ปรับแต่งให้ดีนัก)

     

    แต่อย่างไรก็ดี Hive ไม่ได้ออกแบบมาใช้ทดแทน RDBMS เช่น MySQL/Oracle แต่เหมาะสำหรับการทำงานแบบ Data Warehouse มากกว่า ส่วนเหตุที่ MySQL แม้จะมีจำนวน Core มาก แต่ด้วยข้อจำกัด (โดย Default) ให้การ Group By นั้น จะใช้งานได้แค่ 1 Core เท่านั้น ! [1]

    แต่เมื่อมองในมุมของ Hive สามารถนำเครื่องคอมพิวเตอร์ทั่วไป (Commodity Hardware) มารวมๆกันได้ ก็ทำให้สามารถขยายระบบได้แบบ Scale Out ก็น่าจะเป็นทางเลือกที่น่าสนใจ

    Reference

    [1] https://dev.mysql.com/doc/refman/5.5/en/innodb-performance-thread_concurrency.html

     

  • Ambari #06 การใช้งาน Zeppelin เพื่อเรียกข้อมูลจาก MySQL

    ขั้นตอนการปรับแต่งให้ Zeppelin เชื่อมต่อกับ  MySQL
    ในที่นี้ จะเชื่อมต่อไปยัง MySQL Server: ที่ your.mysql.server โดยมี Username/Password เป็น root/123456 และจะใช้ Database ชื่อ employees

    1. Download https://dev.mysql.com/downloads/connector/j/
      จากนั้นให้ Unzip จะได้ไฟล์ mysql-connector-java-5.1.44-bin.jar (Version อาจจะแตกต่างกัน)
      แล้วนำไปไว้ใน /usr/hdp/current/spark2-client/jars *** ของทุก Nodes ***
    2. จาก Ambari Web UI เลือก Zeppelin Notebook > Quick Links > Zeppelin UI
    3. ที่ Zeppelin UI ให้ Login ด้วย admin/admin
      แล้วคลิก Admin > Interpreter
    4. คลิก Create
      กรอกข้อมูลต่อไปนี้
      Interpreter Name: employees
      Interpreter Group: jdbcแล้วเพิ่ม
      default.driver:  com.mysql.jdbc.Driver
      default.url:       jdbc:mysql://your.mysql.server:3306/employees
      default.user: root
      default.password: 123456ในส่วนของ Dependency ให้กำหนด
      artifact: mysql:mysql-connector-java:5.1.44

      แล้วกดปุ่ม Save

    5. สร้าง Notebook ใหม่ ชื่อ mydatabase
      แล้วเลือก Default Interpreter เป็น employees
      แล้วคลิก Create Note
    6. จากนั้น ก็จะสามารถส่ง Query ต่างได้
    7. รวมถึง แสดง Graph ที่น่าสนใจได้

  • Ambari #05 การดึงข้อมูลเข้าจาก MySQL เข้าสู่ Hive ด้วย Sqoop

    Apache Hive เป็นระบบ Data Warehouse ซึ่งสร้างอยู่บน Hadoop ใช้สำหรับการวิเคราะห์ข้อมูล โดยจุดเด่นคือการใช้คำสั่งภาษา SQL ในการเรียกข้อมูล ทั้งที่อยู่ในรูปแบบของ Database และไฟล์บน Hadoop ได้ เหมาะสำหรับการเก็บข้อมูลขนาดใหญ่มากๆ เช่น การเก็บข้อมูลที่ Rotate ออกจากฐานข้อมูลหลักอย่าง MySQL ก็นำมาเก็บไว้ใน MySQL เพื่อใช้วิเคราะห์ต่อไป

    ในการดึงข้อมูลจาก MySQL จะแบ่งเป็น 2 ส่วน คือ

    1. Full Backup คือการนำข้อมูลทั้งหมดจาก MySQL มาเก็บไว้ใน Apache Hive
    2. Incremental Backup คือการดึงเฉพาะข้อมูลที่เพิ่มขึ้นมาเก็บไว้ใน Apache Hive

    แต่ในบทความนี้ จะแนะนำการทำแบบ Incremental Backup ซึ่งในครั้งแรกจะทำ Full Backup และครั้งต่อๆไป จะทำ Incremental Backup เลย

    Incremental Backup

    sqoop ออกแบบมาให้สามารถ Run คำสั่งให้ทำงานทันทีก็ได้ หรือ จะสร้างเป็น Job เอาไว้ เพื่อใช้งานภายหลังก็ได้ ในที่นี้จะสร้างแบบ Job เพราะให้สะดวกในการทำ Incremental Backup
    การสร้าง Sqoop Job ใช้คำสั่ง

     sqoop job --create JobName -- import ...

    ในส่วนที่จะบอก sqoop ให้ทำงานแบบ incremental backup ต้องตามด้วย Options

    --incremental append 
--check-column your_primary_key

    ในตัวอย่างต่อไปนี้ จะสร้าง sqoop job ชื่อ maillog-incremental
    ใช้คำสั่งต่อไปนี้ เพื่อดึงข้อมูลทั้งหมดจาก your.mysql.server ใน Database: mailscanner จาก Table: maillog โดยสมมุติใช้ username/password เป็น root/123456
    แล้วนำมาใส่ใน Hive Database: mailscanner
    เพิ่มเติมคือ ให้เก็บไว้ในรูปแบบ Paquet File
    และ กำหนดให้ใช้ Field: timestamp เป็น Key เพื่อแบ่งงานเพื่อดึงข้อมูลแบบ Parallel ในที่นี้กำหนดให้แบ่งออกเป็น 10 ส่วนพร้อมๆกัน

    UPDATE: sqoop job จะไม่ยอมให้ใส่ password ตรงๆ แต่จะต้องสร้างไฟล์ password เก็บไว้ใน hdfs และต้องเป็น mode 400 ให้ทำคำสั่งต่อไปนี้ก่อน

    echo -n "123456" > mysqlpassword.txt
hdfs dfs -put mysqlpassword.txt mysqlpassword.txt
hdfs dfs -chmod 400 mysqlpassword.txt

    แล้วจึงสร้าง sqoop job ด้วยคำสั่งต่อไปนี้

    sqoop job --create maillog-incremental 
-- 
import 
--connect jdbc:mysql://your.mysql.server:3306/mailscanner 
--username root 
--password-file /user/hdfs/mysqlpassword.txt 
--table maillog 
--hive-database mailscanner 
--as-parquetfile 
--hive-import 
--split-by timestamp 
-m 10 
--incremental append 
--check-column timestamp

    เมื่อสร้าง sqoop job เสร็จแล้ว สามารถเรียกดูได้ด้วยคำสั่ง

    sqoop job --list

    ดูรายละเอียดการทำงานได้ด้วย คำสั่ง

    sqoop job --show maillog-incremental

    และ สั่งให้ sqoop job ทำงานด้วยคำสั่ง

    sqoop job --exec maillog-incremental

    เมื่อทำการ Run ระบบก็จะแบ่งงานออกเป็น 10 ส่วนให้เครื่องใน Cluster ช่วยกัน Import เข้า Hive

    ในการสั่งานครั้งแรก ระบบจะดึงข้อมูลทั้งหมดมาก่อน เช่น ในตัวอย่างนี้ ดึงมา 12 ล้าน record

    แต่เมื่อสั่งอีกครั้ง ด้วยคำสั่ง

    sqoop job --exec maillog-incremental

    จะดึงเฉพาะส่วนที่เพิ่งเพิ่มเข้าม (233 records ตามภาพ)

    ต่อไป ก็ตั้งเป็น cron ไว้เพื่อทำให้ทำงานทุกๆ 1 ชั่วโมงก็ได้

  • Spark #04 – Pyspark connect to MySQL

    ในบทความนี้ จะกล่าวถึง การดึงข้อมูลจาก MySQL ผ่าน JDBC เพื่อนำมาใช้งานใน Spark ด้วยภาษา Python ซึ่งจะใช้ Library Pyspark

    ในขั้นตอนนี้ขอกล่าวเฉพาะวิธีการก่อน (รายละเอียดจะตามมาทีหลัง)

    1. สร้าง SparkSession ตั้งชื่อว่า myspark 
      from pyspark.sql import SparkSession
myspark = SparkSession \
 .builder \
 .appName("Python Spark SQL basic example") \
 .config("spark.some.config.option", "some-value") \
 .getOrCreate()
    2. ติดต่อ MySQL และสร้าง View ชื่อ myuser 
      myuser=myspark.read.jdbc(url="jdbc:mysql://mysql/mysql",table="user", properties={
 'user': 'user1', 'password': '123456'}
 )
myuser.createOrReplaceTempView(name="myuser")
    3. จากนั้นก็จะสามารถ Query ข้อมูลที่เก็บไว้มาใช้งานใน Spark ได้ 
      myspark.sql(sqlQuery="select user,host from myuser where user='user1'").show()

    ซึ่งต่อจากนี้ จะสามารถใช้ความสามารถของ Spark ซึ่งทำงานด้าน Distributed Computing ได้ดี มาปรับปรุงความเร็วในการ Query ที่ซับซ้อน เช่นการ JOIN ได้ โดยจะกล่าวในบทความต่อๆไป

  • Fixed : KU$_RADM_FPTM_VIEW in invalid status

    • ฐานข้อมูลที่จะพบปัญหานี้ Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
    • OS เป็น CentOS 7.3 หรือ Oracle Enterprise Linux 7.3
    • ปัญหานี้ข้อสำคัญทำให้ export ข้อมูลไม่ได้ คือไม่สามารถสำรองข้อมูลได้
    • เข้าระบบด้วยผู้ใช้ oracle สร้างแฟ้ม fix.sql ด้วยคำสั่ง

    cat >>fix.sql<<EOF
    CREATE OR REPLACE NONEDITIONABLE TYPE "SYS"."KU$_RADM_FPTM_T" as object
    (
    vers_major char(1), /* UDT major version # */
    vers_minor char(1), /* UDT minor version # */
    numbercol number, /* number */
    binfloatcol binary_float, /* binary float */
    bindoublecol binary_double, /* binary double */
    charcol char(1), /* fixed-size character */
    varcharcol varchar2(1), /* variable-size character */
    ncharcol nchar(1), /* fixed-size national character */
    nvarcharcol nvarchar2(1), /* variable-size national character */
    datecol date, /* date */
    ts_col timestamp, /* timestamp */
    tswtz_col timestamp with time zone, /* timestamp with time zone */
    fpver number /* version of default fixed point values */
    )
    /

    CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."KU$_RADM_FPTM_VIEW" OF "SYS"."KU$_RADM_FPTM_T"
    WITH OBJECT IDENTIFIER (fpver) AS
    select '1','0',
    numbercol, binfloatcol, bindoublecol,
    charcol, varcharcol, ncharcol, nvarcharcol,
    datecol,ts_col,tswtz_col, fpver
    from sys.radm_fptm$
    where fpver=1
    /
    EOF

    • เชื่อมต่อกับฐานข้อมูลด้วย sqlplus ด้วยบัญชีผู้ใช้ / as sysdba

    • ที่ prompt SQL> พิมพ์คำสั่ง @/home/oracle/fix.sql
    • ก็จะแก้ปัญหาได้
    • จบ
    • ขอให้สนุก

    ที่มา
    เว็บโดนปิดไปแล้ว

  • การเรียกดูรายการ object ในฐานข้อมูล Oracle

    คำสั่งที่ใช้ในการเรียกดู รายการ object ในฐานข้อมูล Oracle

    • การเรียกดูรายการ object ทั้งหมดในฐานข้อมูล Oracle (เช่น table, view ฯลฯ)  สามารถเรียกดูได้จาก view ที่ชื่อว่า ALL_OBJECTS  ตัวอย่างคำสั่งคือที่ใช้เรียกดูคือ

     

    SELECT * FROM ALL_OBJECTS;

     

    โดยคำสั่งนี้จะแสดงรายการ object ทั้งหมดที่มี

     

    • แต่ในการใช้งานส่วนใหญ่ อาจจะต้องการเจาะจงดูแค่บางเงื่อนไข เช่น ต้องการดูรายการ table ทั้งหมด โดยเจาะจงแค่ schema ใด schema  หนึ่งเท่านั้น  ซึ่งสามารถใช้เงื่อนไขจากฟีลด์ต่อไปนี้ คือ
      • OBJECT_TYPE เป็นการเรียกดูตามเงื่อนไขของประเภท object เช่น หากต้องการดูเฉพาะ table จะใช้เงื่อนไขเป็น  WHERE OBJECT_TYPE = ‘TABLE’
      • OWNER เป็นการเรียกดูเฉพาะเจาะจง schema ใด schema  หนึ่ง เช่น ต้องการเรียกดูข้อมูลจาก schema ชื่อ SCOTT จะใช้เงื่อนไขเป็น WHERE OWNER = ‘SCOTT’
      • ตัวอย่างคำสั่งเต็ม ๆ กรณีที่ต้องการเรียกดูเฉพาะ table ทั้งหมดที่อยู่ใน schema ที่ชื่อ SCOTT

    SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE=’TABLE’ AND OWNER=’SCOTT’;

     

    ตัวอย่างการนำไปใช้ประโยชน์

    เพื่อให้เห็นภาพการนำไปใช้งานจริง จึงขอยกตัวอย่างที่เคยใช้งาน คือ การสร้างสคริปต์คำสั่ง sql ที่อ้างอิงชื่อ table ที่อยู่ใน schema ใด schema หนึ่ง โดยในที่นี้จะยกตัวอย่างการ grant สิทธิ์ในการเข้าถึงข้อมูลของ schema หนึ่งไปให้กับอีก schema หนึ่ง

    • ที่มาที่ไปคือ ในบางครั้ง เราต้องการ grant สิทธิ์ในการเข้าถึงข้อมูลของ schema หนึ่งไปให้กับอีก schema หนึ่ง เช่น ต้องการ grant สิทธิ์การเรียกดูข้อมูลใน table ชื่อ TABLE01 ของ schema ที่ชื่อ SCHEMA01 ให้ schema ที่ชื่อ SCHEMA02 สามารถเรียกดูข้อมูลได้ คำสั่งที่ใช้ในการ grant คือGRANT SELECT ON SCHEMA01.TABLE01 TO SCHEMA02;
    • แต่หากต้องการ grant หลาย ๆ  table ก็ต้องพิมพ์คำสั่งเหล่านี้ทีละคำสั่งซ้ำๆ ไปจนครบตามชื่อ talbe ที่ต้องการ grant ซึ่งถ้า table ที่ต้องการ grant มีเป็นจำนวนมากก็จะยิ่งใช้เวลามากยิ่งขึ้น และอีกปัญหาคือ มีโอกาสพิมพ์ชื่อ table ผิด
    • เทคนิคในการสร้างสคริปต์เพื่อช่วยให้การทำงานรวดเร็วขึ้นจะมีรูปแบบดังนี้คือSELECT ‘GRANT SELECT ON OWNER_SCHEMA.’ || OBJECT_NAME || ‘ TO OTHER_SCHEMA;’
      FROM ALL_OBJECTS
      WHERE OBJECT_TYPE = ‘TABLE’;

      เมื่อ

      • OWNER_SCHEMA คือชื่อของ schema ที่เป็นเจ้าของ table
      • OBJECT_NAME คือชื่อฟีลด์ที่อยู่ใน ALL_OBJECTS โดยเป็นข้อมูลของชื่อ object ซึ่งกรณีนี้คือชื่อของ table นั่นเอง
      • OTHER_SCHEMA คือชื่อของ schema ที่ได้รับการ grant สิทธิ์ให้เรียกดูข้อมูลใน table ของ OWNER_SCHEMA
      • WHERE OBJECT_TYPE = ‘TABLE’ เป็นการระบุเงื่อนไขว่าต้องการ object ที่เป็น table เท่านั้น

    ผลลัพธ์ที่ได้คือ

    GRANT SELECT ON OWNER_SCHEMA.TABLE01 TO OTHER_SCHEMA;

    GRANT SELECT ON OWNER_SCHEMA.TABLE02 TO OTHER_SCHEMA;

    GRANT SELECT ON OWNER_SCHEMA.TABLE03 TO OTHER_SCHEMA;

    GRANT SELECT ON OWNER_SCHEMA.TABLE04 TO OTHER_SCHEMA;

    …….

    …….

    …….

    GRANT SELECT ON OWNER_SCHEMA.TABLE99 TO OTHER_SCHEMA;

     

    โดยคำสั่งทั้งหมดจะเท่ากับจำนวน table ของ OWNER_SCHEMA นั่นเอง ซึ่งหากไม่ต้องการ grant table ไหนก็ลบคำสั่ง grant เฉพาะ table นั้นออกไปได้

    • จะเห็นว่าการใช้เทคนิคลักษณะนี้ จะทำให้สามารถสร้างสคริปต์คำสั่งที่มีลักษณะแบบเดียวกันได้ภายในเวลาอันรวดเร็ว และลดความผิดพลาดของการอ้างอิงชื่อ object ลงไปได้ด้วย
    • อ้างอิงข้อมูล https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2005.htm#i1583352

     

     

     

     

     

  • Oracle: retrieve top n records for each group

    วิธีการเขียน Query เพื่อดึงข้อมูลข้อมูลสูงสุดหรือต่ำสุด N ลำดับแรกของแต่ละกลุ่มออกมาจากตาราง
    สมมติว่าเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 5 ฟิลด์ข้อมูลดังตัวอย่างข้างล่าง
    ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 773 รายการ

    โจทย์: ต้องการดึงข้อมูลนักศึกษาที่ได้คะแนนภาษาอังกฤษสูงสุดแยกตามคณะจากข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 773 รายการนี้

    เริ่มต้นด้วย query ดังนี้

    SELECT a.*,
    ROW_NUMBER ()
    OVER (PARTITION BY fac_id ORDER BY eng_score DESC)
    AS val_row_number
    FROM test_new_student a

    จุดสำคัญของ query ข้างต้นก็คือฟังก์ชัน ROW_NUMBER ซึ่งเป็นฟังก์ชันที่จะให้เลขบรรทัดของผลลัพธ์ออกมาตามการจัดกลุ่มข้อมูลหรือการเรียงลำดับที่เรากำหนดไว้ด้วยคำสั่ง OVER, PARTITION BY และ ORDER BY ที่ตามมา

    จากตัวอย่างข้างต้นหมายความว่า เรากำลัง Select * จากตาราง TEST_NEW_STUDENT พร้อมกับดึงเลข ROW_NUMBER ออกมา โดยเป็นเลขบรรทัดที่ให้แบ่งกลุ่มด้วยคณะ และให้เรียงลำดับด้วยคะแนนภาษาอังกฤษจากมากไปน้อย ซึ่งผลที่ได้จะเป็นดังนี้

    สังเกตที่ฟิลด์ VAL_ROW_NUMBER จะเห็นว่ามันแสดงตามอันดับของคะแนนภาษาอังกฤษ และถูกแบ่งตามคณะอย่างเรียบร้อย แค่นี้เราก็สามารถที่จะ select เอา Top ที่เท่าไหร่ของแต่ละกลุ่มได้แล้ว โดยเลือกเอา VAL_ROW_NUMBER ที่ต้องการ ซึ่งสามารถทำได้โดยเขียน select…where ซ้อน query ข้างต้นเข้าไปอีกทีดังนี้

    SELECT *
    FROM (SELECT a.*,
    ROW_NUMBER ()
    OVER (PARTITION BY fac_id ORDER BY eng_score DESC)
    AS val_row_number
    FROM test_new_student a)
    WHERE val_row_number <= 1;

    ผลลัพธ์:

    สรุปจากความต้องการที่ตั้งไว้ เราสามารถแก้ได้โดยใช้ Window Function ซึ่งมันสามารถหาเลขลำดับบางอย่างภายในกลุ่มข้อมูลออกมาให้ได้รูปแบบทั่วไปของ query คือ

    1 SELECT *,
    2 WFUNCTION OVER (PARTITION BY GROUP_FIELDS ORDER BY ORDER_FIELDS) n
    3 FROM SOURCE

    โดยที่

    SOURCE คือตัวข้อมูล
    WFUNCTION  หมายถึง Window Function ตัวอย่างที่เราเลือกใช้คือ ROW_NUMBER()
    GROUP_FIELDS คือรายการฟิลด์ที่จะแบ่งกลุ่ม เขียนเหมือนตอนที่เราจะ group by สามารถแบ่งด้วยหลายฟิลด์ก็ได้
    ORDER_FIELDS คือรายการฟิลด์ที่ใช้เรียงลำดับ, สามารถเรียงด้วยหลายฟิลด์ก็ได้, ใส่ ASC หรือ DESC ได้เหมือนคำสั่ง order by ปกติเราไม่จำเป็นต้องใส่ทั้ง partition by และ order by อาจจะใส่แค่อันใดอันหนึ่ง แล้วแต่ว่าต้องการแบ่งกลุ่มหรือเรียงลำดับหรือไม่
    n เป็นชื่อ alias ของผลลัพธ์ จะตั้งชื่ออย่างไรก็ได้ เมื่อเราได้เลขลำดับ n ของแต่ละกลุ่มออกมาแล้ว ทีนี้จะเอาไปหา Top N หรือพลิกแพลงยังไง ก็แล้วแต่จะ query ออกมา

  • Oracle: retrieve top n records from a query

    Top-N queries เป็นวิธีการดึงข้อมูลสูงสุดหรือต่ำสุด N ลำดับแรกออกมาจากตาราง โดยวิธีการดึงข้อมูลแบบ Top-N นั้นมีได้หลายวิธี แต่ในบทความนี้จะนำเสนอวิธีการดึงข้อมูลแบบ Top-N records เพียง 3 วิธีการดังนี้

    1. Inline View and ROWNUM
    2. WITH Clause and ROWNUM
    3. ROW_NUMBER

     

    สมมติว่าเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 5 ฟิลด์ข้อมูลดังตัวอย่างข้างล่าง
    ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 773 รายการ

    โจทย์: ต้องการดึงข้อมูลนักศึกษาที่ได้คะแนนภาษาอังกฤษสูงสุด 5 อันดันแรกจากข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 773 รายการนี้

     

    เริ่มต้น Top-N query ตามลำดับเพื่อแก้โจทย์กันค่ะ

    Inline View and ROWNUM
    Classic Top-N style query

    SELECT a.*,rownum
    FROM (SELECT *
    FROM test_new_student
    ORDER BY eng_score desc) a
    WHERE ROWNUM <= 5;

    ผลลัพธ์:

    • จากผลลัพธ์ที่ได้ข้อมูลจะถูกจัดเรียงจากคะแนนจากมากไปน้อยก่อนด้วย ORDER BY clause และหลังจากนั้นก็จะจำกัดจำนวนข้อมูลที่ต้องการด้วย ROWNUM
    • Pseudocolumn ROWNUM เป็นค่าตัวเลขแสดงลำดับที่ของการดึงข้อมูลจากตาราง
    • กรณีที่ต้องการข้อมูลคะแนนภาษาอังกฤษต่ำสุด ใส่ ASC แทน DESC ตรง ORDER BY clause

     

    WITH Clause and ROWNUM
    จากตัวอย่างข้างต้นเรายังสามารถเขียน query ด้วย WITH clause แทนที่ inline view ได้ดังนี้

    WITH ordered_query AS
    (SELECT *
    FROM test_new_student
    ORDER BY eng_score desc)
    SELECT ordered_query.*,rownum
    FROM ordered_query
    WHERE rownum <= 5;

     

    ROW_NUMBER
    ฟังก์ชัน ROW_NUMBER เป็นฟังก์ชันที่กำหนดค่าลำดับของข้อมูลที่จัดเรียงตามข้อมูลที่กำหนดไว้ใน order_by_clause โดยจะมีค่าเริ่มต้นเท่ากับ 1 โดยเราจะมีวิธีการเขียน query ได้ดังนี้

    SELECT *
    FROM (SELECT a.*, row_number() OVER (ORDER BY eng_score DESC) AS val_row_number
    FROM test_new_student a)
    WHERE val_row_number <= 5;

    ผลลัพธ์:

    สังเกตที่ฟิลด์ VAL_ROW_NUMBER จะเห็นว่ามันแสดงตามอันดับของคะแนนภาษาอังกฤษแล้ว แค่นี้เราก็สามารถที่จะ select เอา Top ที่เท่าไหร่ได้แล้ว โดยเลือกเอา VAL_ROW_NUMBER ที่ต้องการ

  • วิธีสร้าง CoreOS Cluster

    จะสร้าง CoreOS ให้กลายเป็น Cluster Docker Container ได้อย่างไร

                 จากบทความที่แล้วที่แนะนำเกี่ยวกับ CoreOS และการติดตั้งบน Vmware[1] ไปแล้วนั้น เราก็สามารถสร้างให้เป็นในรูปแบบ Cluster ได้ โดยมองว่าเครื่องแต่ละเครื่องที่สร้างนั้นเป็น Node หนึ่ง ๆ ใน Cluster โดยใช้ etcd เป็นตัวเก็บข้อมูลของ Node และ Fleet เป็นตัว Deploy docker ให้กระจายไปยัง Node ต่าง ๆ อย่างเหมาะสม โดยที่จะสามารถย้ายตัวเองได้เมื่อมีเครื่องใดเครื่องหนึ่งมีปัญหา (Recommend จำนวนเลขคี่ และอย่างต่ำต้อง 3 node ขึ้นไป ยิ่งเยอะ โอกาสล่มก็ยิ่งต่ำ)


                etcd ในปัจจุบันเป็น Version 3 ซึ่งจะมีประสิทธิภาพเพิ่มขึ้นจาก Version 2 (แต่ใน document web ยังเป็น etcd2 เป็นส่วนมาก) โดยใช้สำหรับเก็บข้อมูลแต่ละ Node ทำให้รู้ว่าในแต่ละ Cluster มีเครื่องใด IP อะไรบ้าง มีทั้งหมด 3 วิธีคือ 

    1. Static เป็นวิธีที่ระบุลงไปเลยในแต่ละเครื่องว่ามีเครื่องไหนบ้างที่อยู่ใน Cluster วิธีการนี้ข้อเสียคือถ้าเพิ่มต้องเพิ่มทุกเครื่อง
    2. etcd Discovery เป็นวิธีที่จะให้ Discovery Service เป็นคนทำหน้าที่เก็บข้อมูล (เหมือน tracker torrent) เมื่อเพิ่มเครื่องใหม่ ก็แค่ชี้ไป Discovery URL ก็เสร็จ
    3. DNS Discovery เป็นวิธีการใช้วิธีการจด DNS ในรูปแบบ SRV record เพื่อบอกว่า บริการนี้มีเครื่องอะไรอยู่บ้าง ซึ่งจะมีการอ้างอิงอยู่กับ Domain Name โดยวิธีนี้จำเป็นต้องจดชื่อ Domain ทุกเครื่อง

                ในบทความนี้จะอธิบายวิธีที่ 1 ซึ่งแม้ยุ่งยาก แต่เหมาะกับระบบที่ Internet Public ไม่ค่อยเสถียร และ ถ้าใครต้องการลองวิธีอื่นสามารถตามอ่านได้ใน Web CoreOS[2] ครับ

      วิธีการตั้งค่า etcd2

    • ทำการสร้าง service etcd2 service ด้วย systemd ดังนี้ 
      sudo vim /etc/systemd/system/etcd2.service
    • ข้อความในไฟล์มีดังนี้ (ถ้าต้องการความปลอดภัยสามารถใช้ https ได้ครับ แต่ต้องมีการทำ certificate เพิ่มเติม ซึ่งไม่ขออธิบายครับ) 
      [Unit]
Description=etcd2
Conflicts=etcd.service

[Service]
User=etcd
Type=notify
Environment=ETCD_DATA_DIR=/var/lib/etcd
ExecStart=/usr/bin/etcd2 --name node01 --initial-advertise-peer-urls http://[IP]:2380 \
 --listen-peer-urls http://[IP]:2380 \
 --listen-client-urls http://[IP]:2379,http://127.0.0.1:2379 \
 --advertise-client-urls http://[IP]:2379 \
 --initial-cluster-token etcd-cluster-1 \
 --initial-cluster node01=http://[IP_node01]:2380,node02=http://[IP_node02]:2380,node03=http://[IP_node03]:2380 \
 --initial-cluster-state new

Restart=always
RestartSec=10s
LimitNOFILE=40000
TimeoutStartSec=0

[Install]
WantedBy=multi-user.target
    • Enable etcd2 service เพื่อให้รันทุกครั้งที่เปิดเครื่อง 
      sudo systemctl enable etcd2
    • Start etcd2 service 
      sudo systemctl start etcd2
    • ตรวจดูสถานะการทำงานของ etcd2 service 
      sudo systemctl status etcd2
    • เราสามารถดูข้อมูลสมาชิกได้ดังนี้ 
      etcdctl member list

     วิธีการตั้งค่า Fleet

    • ทำการสร้าง service fleet โดยการตั้งค่าใน systemd ดังนี้ 
      sudo vim /etc/systemd/system/fleet.service
    • ข้อความในไฟล์มีดังนี้ (จะเห็นว่า config ตั้งค่าให้ Start หลัง etcd2) 
      [Unit]
Description=fleet daemon

After=etcd2.service

Wants=fleet.socket
After=fleet.socket

[Service]
User=fleet
Environment=GOMAXPROCS=1
Environment="FLEET_PUBLIC_IP=[IP]"
ExecStart=/usr/bin/fleetd
Restart=always
RestartSec=10s

[Install]
WantedBy=multi-user.target
    • Enable fleet service เพื่อให้รันทุกครั้งที่เปิดเครื่อง 
      sudo systemctl enable fleet
    • Start fleet service 
      sudo systemctl start fleet
    • ตรวจดูสถานะการทำงานของ fleet service 
      sudo systemctl status fleet
    • วิธีตรวจดูสถานะแต่ละ Node ทำได้ดังนี้ 
      fleetctl list-machines
    • จะได้ผลลัพธ์หน้าตาประมาณนี้ครับ

           ให้ทำการติดตั้งไปเรื่อย ๆ ทั้ง CoreOS->Etcd2->Fleet จนครบ 3 เครื่อง หรือ 5,7,9 เครื่องแล้วแต่จะต้องการว่าจะสร้างกี่ Node ครับ ยกตัวอย่างถ้าครบ 3 เครื่องก็จะได้ประมาณนี้ครับ

    ทดสอบการสร้าง WordPress ผ่าน Fleet[3]

    • วิธีการทำแน่นอนครับ หนีไม่พ้นไฟล์รูปแบบ systemd (อีกแล้ว) แต่ไม่ต้องรันด้วย systemctl นะครับ ทำที่เครื่องใดเครื่องหนึ่ง สร้างที่ /home/core ก็ได้ดังนี้
    • ก่อนอื่นต้องติดตั้ง docker mysql โดยสร้างไฟล์ mysql.service ดังนี้ 
      vim mysql.service
    • ข้อความในไฟล์ประมาณนี้ครับ 
      [Unit]
Description=MySQL DataBase
After=etcd.service
After=docker.service

[Service]
TimeoutStartSec=0
ExecStartPre=-/usr/bin/docker kill mysql
ExecStartPre=-/usr/bin/docker rm mysql
ExecStartPre=/usr/bin/docker pull mysql:5.7
ExecStart=/usr/bin/docker run --name mysql -e MYSQL_ROOT_PASSWORD="wordpress" -e MYSQL_DATABASE="wordpress" -e MYSQL_USER="wordpress" -e MYSQL_PASSWORD="wordpress" mysql:5.7
ExecStop=/usr/bin/docker stop mysql
    • สร้างไฟล์ wordpress.service ดังนี้ 
      vim wordpress.service
    • ข้อความในไฟล์ประมาณนี้ครับ 
      [Unit]
Description=WordPress
After=mysql.service

[Service]
TimeoutStartSec=0
ExecStartPre=-/usr/bin/docker kill wordpress
ExecStartPre=-/usr/bin/docker rm wordpress
ExecStartPre=/usr/bin/docker pull wordpress
ExecStart=/usr/bin/docker run --name wordpress --link mysql -p 8880:80 -e WORDPRESS_DB_PASSWORD=wordpress -e WORDPRESS_DB_NAME=wordpress -e WORDPRESS_DB_USER=wordpress wordpress
ExecStop=/usr/bin/docker stop wordpress

[X-Fleet]
X-ConditionMachineOf=mysql.service
    • สั่ง Start mysql service ด้วย fleetctl ดังนี้ 
      fleetctl start mysql.service

    • สั่ง Start wordpress service ด้วย fleetctl ดังนี้ 
      fleetctl start wordpress.service

    • สั่งคำสั่งเพื่อตรวจสอบสถานะดังนี้ (ซึ่งจะบอกว่าติดตั้งที่ Node ใด และสถานะการใช้งาน หรือการติดตั้งเป็นอย่างไร) 
      fleetctl list-units

    • เสร็จแล้วลองสั่ง fleetctl list-units ที่ Node อื่น ๆ ดูครับก็จะได้ผลลัพธ์เหมือน ๆ กัน
    • ก็จะได้ web wordpress เอาไว้ใช้งานแล้ว
    • จากนั้นทดสอบลองปิด Node ดูครับ สำหรับระบบที่มี 3 Node พังได้แค่ Node เดียวครับ ถ้าอยากได้มากกว่านั้นต้องเพิ่มจำนวน Node ขึ้นไป 
    • จะพบว่าเครื่องจะย้ายไป Start อีก Node ทันที (มันจะสั่ง start ใหม่นะครับ ไม่ได้ย้ายไปแบบ vmware) เท่าที่ทดสอบข้อมูลไม่ได้มาด้วยครับ อีกทั้งยังได้ ip ที่เครื่องใหม่ เพราะฉะนั้นต้องหาวิธีทำ map volume และ proxy web เอาเองครับ

    (Optional) วิธีการ Fix IP แทน DHCP

    • ในกรณีที่ต้องการ Fix IP แทน DHCP ให้เข้าไปสร้าง systemd network config โดยสร้างไฟล์ดังนี้ 
      sudo vim /etc/systemd/network/static.network
    • ข้อความในไฟล์ประมาณนี้ครับ 
      [Match]
Name=[Interface Name]

[Network]
Address=[IP/Mask]
Gateway=[IP Gateway]
DNS=[DNS IP มีหลาย IP ให้เว้นวรรค เช่น 10.0.0.1 10.0.0.2]
    • จากนั้นให้ทำการ Restart เครื่อง (จริง ๆ restart service ก็น่าจะได้ แต่ลองแล้วไม่ได้ครับ)

                 สำหรับ CoreOS Cluster ก็มีเท่านี้ครับ แต่จะเห็นว่ายังขาด Docker Management ที่เป็น GUI รวมถึง Docker Gateway และระบบ Storage ติดตามในตอนต่อ ๆ ไปแล้วกันครับ

    ==================================

    Reference :

    [1] มารู้จักกับ CoreOS Linux และวิธีติดตั้ง CoreOS Linux บน Vmware : https://sysadmin.psu.ac.th/2017/05/03/coreos-linux-install-vmware/

    [2] CoreOS Clustering Guide : https://coreos.com/etcd/docs/latest/op-guide/clustering.html#etcd-discovery

    [3] Deploy WordPress in CoreOS Cluster using Fleet : https://wenfeng-gao.github.io/2016/06/03/deploy-wordpress-in-coreos-cluster-using-fleet.html