Project

Profile

Help

HostedRedmine.com has moved to the Planio platform. All logins and passwords remained the same. All users will be able to login and use Redmine just as before. Read more...

การจัดการ Big DATA

User documentation
2014-12-01

- ถ้า table มีการ update ตลอดเวลา จะนำประมวลผล ออกรายงาน คงทำให้ช้า
ต้อง process ใส่ table report ก่อน ในแต่ละวัน
- การทำ child table เก็บข้อมูลแต่ละเดือน/วัน
โดยใช้
INHERITS
เวลา select จะ ดึงข้อมูลทั้งหมดที่ INHERITS

เวลา insert ให้มช้ rules insert แต่ละ table (เมื่อมีการ insert table แม่ ให้ไป รnsert table ลูกแทน)

postgresql จะ รู้เองว่าต้องไปหา child ไหน โดยอิงจาก rule ที่ insert ตอนแรก
ใช้ วันที่ เป็น key ในการ ค้นหาแต่ละ child

PostgreSQL
http://www.postgresql.org/docs/9.3/static/ddl-inherit.html

1. สร้าง table

-- Table: fsot_overspeed

-- DROP TABLE fsot_overspeed;

CREATE TABLE fsot_overspeed
(
gpsdate timestamp without time zone,
driver_name character varying(40) DEFAULT '---'::character varying,
speed smallint NOT NULL,
overtime_milli bigint NOT NULL,
tambon integer NOT NULL,
amphur integer NOT NULL,
province integer NOT NULL,
mid integer,
vehicle_name character varying(20) DEFAULT '---'::character varying,
speed_limit smallint,
id bigint,
driver_id integer NOT NULL DEFAULT 0,
gprsdate timestamp without time zone
)
WITH (
OIDS=TRUE
);
ALTER TABLE fsot_overspeed OWNER TO postgres;

-- Index: i1fsot_overspeed

-- DROP INDEX i1fsot_overspeed;

CREATE INDEX i1fsot_overspeed
ON fsot_overspeed
USING btree
(mid, gprsdate)
TABLESPACE tblspc_fsot_m01;

-- Index: i2fsot_overspeed

-- DROP INDEX i2fsot_overspeed;

CREATE INDEX i2fsot_overspeed
ON fsot_overspeed
USING btree
(mid, gpsdate)
TABLESPACE tblspc_fsot_m01;

-- Index: i3fsot_overspeed

-- DROP INDEX i3fsot_overspeed;

CREATE INDEX i3fsot_overspeed
ON fsot_overspeed
USING btree
(date(gpsdate))
TABLESPACE tblspc_fsot_m01;

-- Index: overspeed_mid_overtimemilli_gps

-- DROP INDEX overspeed_mid_overtimemilli_gps;

CREATE INDEX overspeed_mid_overtimemilli_gps
ON fsot_overspeed
USING btree
(mid, overtime_milli, gpsdate);

-- Rule: fsot_overspeed_insert_01 ON fsot_overspeed

-- DROP RULE fsot_overspeed_insert_01 ON fsot_overspeed;

CREATE OR REPLACE RULE fsot_overspeed_insert_01 AS
ON INSERT TO fsot_overspeed
WHERE to_char(new.gpsdate, 'MM'::text) = '01'::text DO INSTEAD INSERT INTO fsot_overspeed_01 (gpsdate, driver_name, speed, overtime_milli, tambon, amphur, province, mid, vehicle_name, speed_limit, id, driver_id, gprsdate)
VALUES (new.gpsdate, new.driver_name, new.speed, new.overtime_milli, new.tambon, new.amphur, new.province, new.mid, new.vehicle_name, new.speed_limit, new.id, new.driver_id, new.gprsdate);

-- Rule: fsot_overspeed_insert_02 ON fsot_overspeed

-- DROP RULE fsot_overspeed_insert_02 ON fsot_overspeed;

CREATE OR REPLACE RULE fsot_overspeed_insert_02 AS
ON INSERT TO fsot_overspeed
WHERE to_char(new.gpsdate, 'MM'::text) = '02'::text DO INSTEAD INSERT INTO fsot_overspeed_02 (gpsdate, driver_name, speed, overtime_milli, tambon, amphur, province, mid, vehicle_name, speed_limit, id, driver_id, gprsdate)
VALUES (new.gpsdate, new.driver_name, new.speed, new.overtime_milli, new.tambon, new.amphur, new.province, new.mid, new.vehicle_name, new.speed_limit, new.id, new.driver_id, new.gprsdate);

-- Rule: fsot_overspeed_insert_03 ON fsot_overspeed

-- DROP RULE fsot_overspeed_insert_03 ON fsot_overspeed;

CREATE OR REPLACE RULE fsot_overspeed_insert_03 AS
ON INSERT TO fsot_overspeed
WHERE to_char(new.gpsdate, 'MM'::text) = '03'::text DO INSTEAD INSERT INTO fsot_overspeed_03 (gpsdate, driver_name, speed, overtime_milli, tambon, amphur, province, mid, vehicle_name, speed_limit, id, driver_id, gprsdate)
VALUES (new.gpsdate, new.driver_name, new.speed, new.overtime_milli, new.tambon, new.amphur, new.province, new.mid, new.vehicle_name, new.speed_limit, new.id, new.driver_id, new.gprsdate);

-- Rule: fsot_overspeed_insert_04 ON fsot_overspeed

-- DROP RULE fsot_overspeed_insert_04 ON fsot_overspeed;

CREATE OR REPLACE RULE fsot_overspeed_insert_04 AS
ON INSERT TO fsot_overspeed
WHERE to_char(new.gpsdate, 'MM'::text) = '04'::text DO INSTEAD INSERT INTO fsot_overspeed_04 (gpsdate, driver_name, speed, overtime_milli, tambon, amphur, province, mid, vehicle_name, speed_limit, id, driver_id, gprsdate)
VALUES (new.gpsdate, new.driver_name, new.speed, new.overtime_milli, new.tambon, new.amphur, new.province, new.mid, new.vehicle_name, new.speed_limit, new.id, new.driver_id, new.gprsdate);

-- Rule: fsot_overspeed_insert_05 ON fsot_overspeed

-- DROP RULE fsot_overspeed_insert_05 ON fsot_overspeed;

CREATE OR REPLACE RULE fsot_overspeed_insert_05 AS
ON INSERT TO fsot_overspeed
WHERE to_char(new.gpsdate, 'MM'::text) = '05'::text DO INSTEAD INSERT INTO fsot_overspeed_05 (gpsdate, driver_name, speed, overtime_milli, tambon, amphur, province, mid, vehicle_name, speed_limit, id, driver_id, gprsdate)
VALUES (new.gpsdate, new.driver_name, new.speed, new.overtime_milli, new.tambon, new.amphur, new.province, new.mid, new.vehicle_name, new.speed_limit, new.id, new.driver_id, new.gprsdate);

-- Rule: fsot_overspeed_insert_06 ON fsot_overspeed

-- DROP RULE fsot_overspeed_insert_06 ON fsot_overspeed;

CREATE OR REPLACE RULE fsot_overspeed_insert_06 AS
ON INSERT TO fsot_overspeed
WHERE to_char(new.gpsdate, 'MM'::text) = '06'::text DO INSTEAD INSERT INTO fsot_overspeed_06 (gpsdate, driver_name, speed, overtime_milli, tambon, amphur, province, mid, vehicle_name, speed_limit, id, driver_id, gprsdate)
VALUES (new.gpsdate, new.driver_name, new.speed, new.overtime_milli, new.tambon, new.amphur, new.province, new.mid, new.vehicle_name, new.speed_limit, new.id, new.driver_id, new.gprsdate);

-- Rule: fsot_overspeed_insert_07 ON fsot_overspeed

-- DROP RULE fsot_overspeed_insert_07 ON fsot_overspeed;

CREATE OR REPLACE RULE fsot_overspeed_insert_07 AS
ON INSERT TO fsot_overspeed
WHERE to_char(new.gpsdate, 'MM'::text) = '07'::text DO INSTEAD INSERT INTO fsot_overspeed_07 (gpsdate, driver_name, speed, overtime_milli, tambon, amphur, province, mid, vehicle_name, speed_limit, id, driver_id, gprsdate)
VALUES (new.gpsdate, new.driver_name, new.speed, new.overtime_milli, new.tambon, new.amphur, new.province, new.mid, new.vehicle_name, new.speed_limit, new.id, new.driver_id, new.gprsdate);

-- Rule: fsot_overspeed_insert_08 ON fsot_overspeed

-- DROP RULE fsot_overspeed_insert_08 ON fsot_overspeed;

CREATE OR REPLACE RULE fsot_overspeed_insert_08 AS
ON INSERT TO fsot_overspeed
WHERE to_char(new.gpsdate, 'MM'::text) = '08'::text DO INSTEAD INSERT INTO fsot_overspeed_08 (gpsdate, driver_name, speed, overtime_milli, tambon, amphur, province, mid, vehicle_name, speed_limit, id, driver_id, gprsdate)
VALUES (new.gpsdate, new.driver_name, new.speed, new.overtime_milli, new.tambon, new.amphur, new.province, new.mid, new.vehicle_name, new.speed_limit, new.id, new.driver_id, new.gprsdate);

-- Rule: fsot_overspeed_insert_09 ON fsot_overspeed

-- DROP RULE fsot_overspeed_insert_09 ON fsot_overspeed;

CREATE OR REPLACE RULE fsot_overspeed_insert_09 AS
ON INSERT TO fsot_overspeed
WHERE to_char(new.gpsdate, 'MM'::text) = '09'::text DO INSTEAD INSERT INTO fsot_overspeed_09 (gpsdate, driver_name, speed, overtime_milli, tambon, amphur, province, mid, vehicle_name, speed_limit, id, driver_id, gprsdate)
VALUES (new.gpsdate, new.driver_name, new.speed, new.overtime_milli, new.tambon, new.amphur, new.province, new.mid, new.vehicle_name, new.speed_limit, new.id, new.driver_id, new.gprsdate);

-- Rule: fsot_overspeed_insert_10 ON fsot_overspeed

-- DROP RULE fsot_overspeed_insert_10 ON fsot_overspeed;

CREATE OR REPLACE RULE fsot_overspeed_insert_10 AS
ON INSERT TO fsot_overspeed
WHERE to_char(new.gpsdate, 'MM'::text) = '10'::text DO INSTEAD INSERT INTO fsot_overspeed_10 (gpsdate, driver_name, speed, overtime_milli, tambon, amphur, province, mid, vehicle_name, speed_limit, id, driver_id, gprsdate)
VALUES (new.gpsdate, new.driver_name, new.speed, new.overtime_milli, new.tambon, new.amphur, new.province, new.mid, new.vehicle_name, new.speed_limit, new.id, new.driver_id, new.gprsdate);

-- Rule: fsot_overspeed_insert_11 ON fsot_overspeed

-- DROP RULE fsot_overspeed_insert_11 ON fsot_overspeed;

CREATE OR REPLACE RULE fsot_overspeed_insert_11 AS
ON INSERT TO fsot_overspeed
WHERE to_char(new.gpsdate, 'MM'::text) = '11'::text DO INSTEAD INSERT INTO fsot_overspeed_11 (gpsdate, driver_name, speed, overtime_milli, tambon, amphur, province, mid, vehicle_name, speed_limit, id, driver_id, gprsdate)
VALUES (new.gpsdate, new.driver_name, new.speed, new.overtime_milli, new.tambon, new.amphur, new.province, new.mid, new.vehicle_name, new.speed_limit, new.id, new.driver_id, new.gprsdate);

-- Rule: fsot_overspeed_insert_12 ON fsot_overspeed

-- DROP RULE fsot_overspeed_insert_12 ON fsot_overspeed;

CREATE OR REPLACE RULE fsot_overspeed_insert_12 AS
ON INSERT TO fsot_overspeed
WHERE to_char(new.gpsdate, 'MM'::text) = '12'::text DO INSTEAD INSERT INTO fsot_overspeed_12 (gpsdate, driver_name, speed, overtime_milli, tambon, amphur, province, mid, vehicle_name, speed_limit, id, driver_id, gprsdate)
VALUES (new.gpsdate, new.driver_name, new.speed, new.overtime_milli, new.tambon, new.amphur, new.province, new.mid, new.vehicle_name, new.speed_limit, new.id, new.driver_id, new.gprsdate);

2. create child

-- Table: fsot_overspeed_01

-- DROP TABLE fsot_overspeed_01;

CREATE TABLE fsot_overspeed_01
(
gpsdate timestamp without time zone,
driver_name character varying(40) DEFAULT '---'::character varying,
speed smallint NOT NULL,
overtime_milli bigint NOT NULL,
tambon integer NOT NULL,
amphur integer NOT NULL,
province integer NOT NULL,
mid integer,
vehicle_name character varying(20) DEFAULT '---'::character varying,
speed_limit smallint,
id bigint,
driver_id integer NOT NULL DEFAULT 0,
gprsdate timestamp without time zone,
CONSTRAINT fsot_overspeed_01_gpsdate_check CHECK (to_char(gpsdate, 'MM'::text) = '01'::text)
)
INHERITS (fsot_overspeed)
WITH (
OIDS=TRUE
)
TABLESPACE tblspc_fsot_m01;
ALTER TABLE fsot_overspeed_01 OWNER TO postgres;

-- Index: i1fsot_overspeed_01

-- DROP INDEX i1fsot_overspeed_01;

CREATE INDEX i1fsot_overspeed_01
ON fsot_overspeed_01
USING btree
(mid, gprsdate)
TABLESPACE tblspc_fsot_m01;

-- Index: i2fsot_overspeed_01

-- DROP INDEX i2fsot_overspeed_01;

CREATE INDEX i2fsot_overspeed_01
ON fsot_overspeed_01
USING btree
(mid, gpsdate)
TABLESPACE tblspc_fsot_m01;

-- Index: i3fsot_overspeed_01

-- DROP INDEX i3fsot_overspeed_01;

CREATE INDEX i3fsot_overspeed_01
ON fsot_overspeed_01
USING btree
(date(gpsdate))
TABLESPACE tblspc_fsot_m01;

3. create child 1-12 months or 365 days

-- Table: fsot_overspeed_12

-- DROP TABLE fsot_overspeed_12;

CREATE TABLE fsot_overspeed_12
(
gpsdate timestamp without time zone,
driver_name character varying(40) DEFAULT '---'::character varying,
speed smallint NOT NULL,
overtime_milli bigint NOT NULL,
tambon integer NOT NULL,
amphur integer NOT NULL,
province integer NOT NULL,
mid integer,
vehicle_name character varying(20) DEFAULT '---'::character varying,
speed_limit smallint,
id bigint,
driver_id integer NOT NULL DEFAULT 0,
gprsdate timestamp without time zone,
CONSTRAINT fsot_overspeed_12_gpsdate_check CHECK (to_char(gpsdate, 'MM'::text) = '12'::text)
)
INHERITS (fsot_overspeed)
WITH (
OIDS=TRUE
)
TABLESPACE tblspc_fsot_m12;
ALTER TABLE fsot_overspeed_12 OWNER TO postgres;

-- Index: i1fsot_overspeed_12

-- DROP INDEX i1fsot_overspeed_12;

CREATE INDEX i1fsot_overspeed_12
ON fsot_overspeed_12
USING btree
(mid, gprsdate)
TABLESPACE tblspc_fsot_m12;

-- Index: i2fsot_overspeed_12

-- DROP INDEX i2fsot_overspeed_12;

CREATE INDEX i2fsot_overspeed_12
ON fsot_overspeed_12
USING btree
(mid, gpsdate)
TABLESPACE tblspc_fsot_m12;

-- Index: i3fsot_overspeed_12

-- DROP INDEX i3fsot_overspeed_12;

CREATE INDEX i3fsot_overspeed_12
ON fsot_overspeed_12
USING btree
(date(gpsdate))
TABLESPACE tblspc_fsot_m12;

Files