hit-lacus edited a comment on issue #902: KYLIN-3832 Query pushdown support postgresql URL: https://github.com/apache/kylin/pull/902#issuecomment-547393171 # Test ### Test prepare - postgres:9.4 - CDH 5.7 - Kylin build from master branch ```sh ## start postgres server docker run --name postgres1 -e POSTGRES_PASSWORD=password -p 54321:5432 -d postgres:9.4 ## connect to postgres psql -U postgres -h 10.1.0.29 -p 54321 ``` ### Test data ```sql create database test_db5; -- change to that database \c test_db5 -- create two more schema create schema sc1; create schema sc2; -- set search_path SET search_path = public, sc1, sc2; CREATE TYPE mpaa_rating AS ENUM ( 'G', 'PG', 'PG-13', 'R', 'NC-17' ); -- It is a fact table, and Watch_time is the partition date column CREATE TABLE sc1.Film_Play ( Audience_id integer NOT NULL, Film_id integer NOT NULL, Watch_time timestamp with time zone DEFAULT now() NOT NULL, payment integer NOT NULL ); -- It is a dimension table CREATE TABLE Film ( FilM_id integer DEFAULT 1 NOT NULL, Title text NOT NULL, Description text, Release_Year smallint, Language_id smallint NOT NULL, Original_language_id smallint, Rental_Duration smallint DEFAULT 3 NOT NULL, "FroM" numeric(4,2) DEFAULT 4.99 NOT NULL, "Where" smallint, Replacement_cost numeric(5,2) DEFAULT 19.99 NOT NULL, Rating mpaa_rating DEFAULT 'G'::mpaa_rating, Last_update timestamp with time zone DEFAULT now() NOT NULL, Special_features text[], Fulltext tsvector NOT NULL ); -- It is a dimension table too CREATE TABLE sc2.Audience ( audience_id integer DEFAULT 0 NOT NULL, First_name text NOT NULL, Last_name text NOT NULL, Last_update timestamp with time zone DEFAULT now() NOT NULL ); -- insert some data INSERT INTO audience VALUES (1, 'PENELOPE', 'GUINESS', '2017-02-15 09:34:33'); INSERT INTO audience VALUES (2, 'NICK', 'WAHLBERG', '2017-02-15 09:34:33'); INSERT INTO audience VALUES (3, 'ED', 'CHASE', '2017-02-15 09:34:33'); INSERT INTO audience VALUES (4, 'JENNIFER', 'DAVIS', '2017-02-15 09:34:33'); INSERT INTO audience VALUES (5, 'JOHNNY', 'LOLLOBRIGIDA', '2017-02-15 09:34:33'); INSERT INTO audience VALUES (6, 'BETTE', 'NICHOLSON', '2017-02-15 09:34:33'); INSERT INTO audience VALUES (7, 'GRACE', 'MOSTEL', '2017-02-15 09:34:33'); INSERT INTO audience VALUES (8, 'MATTHEW', 'JOHANSSON', '2017-02-15 09:34:33'); INSERT INTO audience VALUES (9, 'JOE', 'SWANK', '2017-02-15 09:34:33'); INSERT INTO audience VALUES (10, 'CHRISTIAN', 'GABLE', '2017-02-15 09:34:33'); INSERT INTO film VALUES (1, 'ACADEMY DINOSAUR', 'A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies', 2006, 1, NULL, 6, 0.99, 86, 20.99, 'PG', '2017-09-10 17:46:03.905795', '{"Deleted Scenes","Behind the Scenes"}', '''academi'':1 ''battl'':15 ''canadian'':20 ''dinosaur'':2 ''drama'':5 ''epic'':4 ''feminist'':8 ''mad'':11 ''must'':14 ''rocki'':21 ''scientist'':12 ''teacher'':17'); INSERT INTO film VALUES (2, 'ACE GOLDFINGER', 'A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China', 2006, 1, NULL, 3, 4.99, 48, 12.99, 'G', '2017-09-10 17:46:03.905795', '{Trailers,"Deleted Scenes"}', '''ace'':1 ''administr'':9 ''ancient'':19 ''astound'':4 ''car'':17 ''china'':20 ''databas'':8 ''epistl'':5 ''explor'':12 ''find'':15 ''goldfing'':2 ''must'':14'); INSERT INTO film VALUES (3, 'ADAPTATION HOLES', 'A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Faudiencey', 2006, 1, NULL, 7, 2.99, 50, 18.99, 'NC-17', '2017-09-10 17:46:03.905795', '{Trailers,"Deleted Scenes"}', '''adapt'':1 ''astound'':4 ''baloon'':19 ''car'':11 ''faudiencei'':20 ''hole'':2 ''lumberjack'':8,16 ''must'':13 ''reflect'':5 ''sink'':14'); INSERT INTO film VALUES (4, 'AFFAIR PREJUDICE', 'A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank', 2006, 1, NULL, 5, 2.99, 117, 26.99, 'G', '2017-09-10 17:46:03.905795', '{Commentaries,"Behind the Scenes"}', '''affair'':1 ''chase'':14 ''documentari'':5 ''fanci'':4 ''frisbe'':8 ''lumberjack'':11 ''monkey'':16 ''must'':13 ''prejudic'':2 ''shark'':19 ''tank'':20'); INSERT INTO film VALUES (5, 'AFRICAN EGG', 'A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico', 2006, 1, NULL, 6, 2.99, 130, 22.99, 'G', '2017-09-10 17:46:03.905795', '{"Deleted Scenes"}', '''african'':1 ''chef'':11 ''dentist'':14 ''documentari'':7 ''egg'':2 ''fast'':5 ''fast-pac'':4 ''forens'':19 ''gulf'':23 ''mexico'':25 ''must'':16 ''pace'':6 ''pastri'':10 ''psychologist'':20 ''pursu'':17'); INSERT INTO film VALUES (6, 'AGENT TRUMAN', 'A Intrepid Panorama of a Robot And a Boy who must Escape a Sumo Wrestler in Ancient China', 2006, 1, NULL, 3, 2.99, 169, 17.99, 'PG', '2017-09-10 17:46:03.905795', '{"Deleted Scenes"}', '''agent'':1 ''ancient'':19 ''boy'':11 ''china'':20 ''escap'':14 ''intrepid'':4 ''must'':13 ''panorama'':5 ''robot'':8 ''sumo'':16 ''truman'':2 ''wrestler'':17'); INSERT INTO film VALUES (7, 'AIRPLANE SIERRA', 'A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet Boat', 2006, 1, NULL, 6, 4.99, 62, 28.99, 'PG-13', '2017-09-10 17:46:03.905795', '{Trailers,"Deleted Scenes"}', '''airplan'':1 ''boat'':20 ''butler'':11,16 ''discov'':14 ''hunter'':8 ''jet'':19 ''must'':13 ''saga'':5 ''sierra'':2 ''touch'':4'); INSERT INTO film VALUES (8, 'AIRPORT POLLOCK', 'A Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India', 2006, 1, NULL, 6, 4.99, 54, 15.99, 'R', '2017-09-10 17:46:03.905795', '{Trailers}', '''airport'':1 ''ancient'':18 ''confront'':14 ''epic'':4 ''girl'':11 ''india'':19 ''monkey'':16 ''moos'':8 ''must'':13 ''pollock'':2 ''tale'':5'); INSERT INTO film VALUES (9, 'ALABAMA DEVIL', 'A Thoughtful Panorama of a Database Administrator And a Mad Scientist who must Outgun a Mad Scientist in A Jet Boat', 2006, 1, NULL, 3, 2.99, 114, 21.99, 'PG-13', '2017-09-10 17:46:03.905795', '{Trailers,"Deleted Scenes"}', '''administr'':9 ''alabama'':1 ''boat'':23 ''databas'':8 ''devil'':2 ''jet'':22 ''mad'':12,18 ''must'':15 ''outgun'':16 ''panorama'':5 ''scientist'':13,19 ''thought'':4'); INSERT INTO film VALUES (10, 'ALADDIN CALENDAR', 'A Action-Packed Tale of a Man And a Lumberjack who must Reach a Feminist in Ancient China', 2006, 1, NULL, 6, 4.99, 63, 24.99, 'NC-17', '2017-09-10 17:46:03.905795', '{Trailers,"Deleted Scenes"}', '''action'':5 ''action-pack'':4 ''aladdin'':1 ''ancient'':20 ''calendar'':2 ''china'':21 ''feminist'':18 ''lumberjack'':13 ''man'':10 ''must'':15 ''pack'':6 ''reach'':16 ''tale'':7'); INSERT INTO film_play VALUES (1, 1, '2017-01-15 10:05:03', 30); INSERT INTO film_play VALUES (1, 2, '2017-02-15 10:05:03', 25); INSERT INTO film_play VALUES (1, 3, '2017-03-15 10:05:03', 31); INSERT INTO film_play VALUES (1, 4, '2017-04-15 10:05:03', 25); INSERT INTO film_play VALUES (1, 5, '2017-05-15 10:05:03', 41); INSERT INTO film_play VALUES (1, 6, '2017-06-15 10:05:03', 33); INSERT INTO film_play VALUES (1, 7, '2017-07-15 10:05:03', 41); INSERT INTO film_play VALUES (2, 1, '2017-01-15 10:05:03', 30); INSERT INTO film_play VALUES (2, 2, '2017-02-15 10:05:03', 25); INSERT INTO film_play VALUES (2, 3, '2017-03-15 10:05:03', 31); INSERT INTO film_play VALUES (2, 4, '2017-04-15 10:05:03', 25); INSERT INTO film_play VALUES (2, 5, '2017-05-15 10:05:03', 41); INSERT INTO film_play VALUES (2, 6, '2017-06-15 10:05:03', 33); INSERT INTO film_play VALUES (2, 7, '2017-07-15 10:05:03', 41); INSERT INTO film_play VALUES (3, 1, '2017-01-15 10:05:03', 30); INSERT INTO film_play VALUES (3, 2, '2017-02-15 10:05:03', 25); INSERT INTO film_play VALUES (3, 3, '2017-03-15 10:05:03', 31); INSERT INTO film_play VALUES (3, 4, '2017-04-15 10:05:03', 25); INSERT INTO film_play VALUES (3, 5, '2017-05-15 10:05:03', 41); INSERT INTO film_play VALUES (3, 6, '2017-06-15 10:05:03', 33); INSERT INTO film_play VALUES (3, 7, '2017-07-15 10:05:03', 41); INSERT INTO film_play VALUES (4, 1, '2017-01-15 10:05:03', 30); INSERT INTO film_play VALUES (4, 2, '2017-02-15 10:05:03', 25); INSERT INTO film_play VALUES (4, 3, '2017-03-15 10:05:03', 31); INSERT INTO film_play VALUES (4, 4, '2017-04-15 10:05:03', 25); INSERT INTO film_play VALUES (4, 5, '2017-05-15 10:05:03', 41); INSERT INTO film_play VALUES (4, 6, '2017-06-15 10:05:03', 33); INSERT INTO film_play VALUES (4, 7, '2017-07-15 10:05:03', 41); INSERT INTO film_play VALUES (5, 1, '2017-01-15 10:05:03', 30); INSERT INTO film_play VALUES (5, 2, '2017-02-15 10:05:03', 25); INSERT INTO film_play VALUES (5, 3, '2017-03-15 10:05:03', 31); INSERT INTO film_play VALUES (5, 4, '2017-04-15 10:05:03', 25); INSERT INTO film_play VALUES (5, 5, '2017-05-15 10:05:03', 41); INSERT INTO film_play VALUES (5, 6, '2017-06-15 10:05:03', 33); INSERT INTO film_play VALUES (5, 7, '2017-07-15 10:05:03', 41); INSERT INTO film_play VALUES (6, 1, '2017-01-15 10:05:03', 30); INSERT INTO film_play VALUES (6, 2, '2017-02-15 10:05:03', 25); INSERT INTO film_play VALUES (6, 3, '2017-03-15 10:05:03', 31); INSERT INTO film_play VALUES (6, 4, '2017-04-15 10:05:03', 25); INSERT INTO film_play VALUES (6, 5, '2017-05-15 10:05:03', 41); INSERT INTO film_play VALUES (6, 6, '2017-06-15 10:05:03', 33); INSERT INTO film_play VALUES (6, 7, '2017-07-15 10:05:03', 41); INSERT INTO film_play VALUES (7, 1, '2017-01-15 10:05:03', 30); INSERT INTO film_play VALUES (7, 2, '2017-02-15 10:05:03', 25); INSERT INTO film_play VALUES (7, 3, '2017-03-15 10:05:03', 31); INSERT INTO film_play VALUES (7, 4, '2017-04-15 10:05:03', 25); INSERT INTO film_play VALUES (7, 5, '2017-05-15 10:05:03', 41); INSERT INTO film_play VALUES (7, 6, '2017-06-15 10:05:03', 33); INSERT INTO film_play VALUES (7, 7, '2017-07-15 10:05:03', 41); -- check all table \dt -- check all schema \dn -- select * from film limit 2; -- select * from audience limit 2; -- select * from film_play limit 2; ```
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services
