| -- start_ignore |
| SET SEARCH_PATH=TestExternalTable_TestExternalTableAll; |
| SET |
| -- end_ignore |
| -- |
| -- external tables 1 - short and simple functional tests. The full set of tests |
| -- exists in cdbunit. |
| -- |
| -- start_matchsubs |
| -- |
| -- # replace return code in error message (platform specific) |
| -- |
| -- m/ERROR\:\s+external table .* command ended with .* not found/ |
| -- s/nosuchcommand\:\s*(command)? not found/nosuchcommand\: NOT FOUND/ |
| -- |
| -- m/ERROR\:\s+external table .* command ended with .*No such file.*/ |
| -- s/nosuchfile\.txt\:\s*No such file (or directory)?/nosuchfile\.txt\: NO SUCH FILE/ |
| -- m/ERROR\:\s+external table .* command ended with .*No such file.*/i |
| -- s/cat\: (cannot open)? nosuchfile\.txt/cat\: nosuchfile\.txt/ |
| -- |
| -- # remove line number - redhat |
| -- m/ERROR\:\s+external table .* command ended with .*NOT FOUND.*/i |
| -- s/\s+line \d+\:// |
| -- # remove cannot open - solaris |
| -- m/ERROR\:\s+external table .* command ended with .*cat\: cannot open.*/i |
| -- s/cat\: cannot open (.*)$/cat\: $1\: NO SUCH FILE/ |
| -- |
| -- end_matchsubs |
| SET gp_foreign_data_access = true; |
| SET |
| set optimizer_disable_missing_stats_collection = on; |
| SET |
| CREATE TABLE REG_REGION (R_REGIONKEY INT, R_NAME CHAR(25), R_COMMENT VARCHAR(152)) DISTRIBUTED BY (R_REGIONKEY); |
| CREATE TABLE |
| -- start_ignore |
| -- -------------------------------------- |
| -- check platform |
| -- -------------------------------------- |
| drop external web table if exists check_echo; |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:36: NOTICE: table "check_echo" does not exist, skipping |
| DROP EXTERNAL TABLE |
| CREATE EXTERNAL WEB TABLE check_echo (x text) |
| execute E'(echo gpfdist)' |
| on SEGMENT 0 |
| format 'text'; |
| CREATE EXTERNAL TABLE |
| select * from check_echo; |
| x |
| --------- |
| gpfdist |
| (1 row) |
| |
| -- end_ignore |
| -- -------------------------------------- |
| -- 'gpfdist' protocol |
| -- -------------------------------------- |
| CREATE EXTERNAL WEB TABLE gpfdist_status (x text) |
| execute E'( python $GPHOME/bin/lib/gppinggpfdist.py @hostname@:7070 2>&1 || echo) ' |
| on SEGMENT 0 |
| FORMAT 'text' (delimiter '|'); |
| CREATE EXTERNAL TABLE |
| CREATE EXTERNAL WEB TABLE gpfdist_start (x text) |
| execute E'((@gpwhich_gpfdist@ -p 7070 -d @abs_srcdir@/data </dev/null >/dev/null 2>&1 &); sleep 2; echo "starting...") ' |
| on SEGMENT 0 |
| FORMAT 'text' (delimiter '|'); |
| CREATE EXTERNAL TABLE |
| CREATE EXTERNAL WEB TABLE gpfdist_stop (x text) |
| execute E'(/bin/pkill gpfdist || killall gpfdist) > /dev/null 2>&1; echo "stopping..."' |
| on SEGMENT 0 |
| FORMAT 'text' (delimiter '|'); |
| CREATE EXTERNAL TABLE |
| -- start_ignore |
| select * from gpfdist_stop; |
| x |
| ------------- |
| stopping... |
| (1 row) |
| |
| select * from gpfdist_status; |
| x |
| ------------------------------------------------------ |
| Error: gpfdist is not running (reason: socket error) |
| Exit: 1 |
| |
| (3 rows) |
| |
| select * from gpfdist_start; |
| x |
| ------------- |
| starting... |
| (1 row) |
| |
| select * from gpfdist_status; |
| x |
| ------------------------------------------------------------------------- |
| Okay, gpfdist version "2.0.1.0 build dev" is running on @hostname@:7070. |
| (1 row) |
| |
| -- end_ignore |
| CREATE EXTERNAL TABLE EXT_NATION ( N_NATIONKEY INTEGER , |
| N_NAME CHAR(25) , |
| N_REGIONKEY INTEGER , |
| N_COMMENT VARCHAR(152)) |
| location ('gpfdist://@hostname@:7070/nation.tbl' ) |
| FORMAT 'text' (delimiter '|'); |
| CREATE EXTERNAL TABLE |
| CREATE EXTERNAL TABLE EXT_REGION (LIKE REG_REGION) |
| location ('gpfdist://@hostname@:7070/region.tbl' ) |
| FORMAT 'text' (delimiter '|'); |
| CREATE EXTERNAL TABLE |
| SELECT * FROM EXT_NATION; |
| n_nationkey | n_name | n_regionkey | n_comment |
| -------------+---------------------------+-------------+-------------------------------------------------------------------------------------------------------------------- |
| 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai |
| 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon |
| 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special |
| 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold |
| 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d |
| 5 | ETHIOPIA | 0 | ven packages wake quickly. regu |
| 6 | FRANCE | 3 | refully final requests. regular, ironi |
| 7 | GERMANY | 3 | l platelets. regular accounts x-ray: unusual, regular acco |
| 8 | INDIA | 2 | ss excuses cajole slyly across the packages. deposits print aroun |
| 9 | INDONESIA | 2 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull |
| 10 | IRAN | 4 | efully alongside of the slyly final dependencies. |
| 11 | IRAQ | 4 | nic deposits boost atop the quickly final requests? quickly regula |
| 12 | JAPAN | 2 | ously. final, express gifts cajole a |
| 13 | JORDAN | 4 | ic deposits are blithely about the carefully regular pa |
| 14 | KENYA | 0 | pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t |
| 15 | MOROCCO | 0 | rns. blithely bold courts among the closely regular packages use furiously bold platelets? |
| 16 | MOZAMBIQUE | 0 | s. ironic, unusual asymptotes wake blithely r |
| 17 | PERU | 1 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun |
| 18 | CHINA | 2 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos |
| 19 | ROMANIA | 3 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account |
| 20 | SAUDI ARABIA | 4 | ts. silent requests haggle. closely express packages sleep across the blithely |
| 21 | VIETNAM | 2 | hely enticingly express accounts. even, final |
| 22 | RUSSIA | 3 | requests against the platelets use never according to the quickly regular pint |
| 23 | UNITED KINGDOM | 3 | eans boost carefully special requests. accounts are. carefull |
| 24 | UNITED STATES | 1 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be |
| (25 rows) |
| |
| SELECT * FROM EXT_REGION; |
| r_regionkey | r_name | r_comment |
| -------------+---------------------------+--------------------------------------------------------------------------------------------------------------------- |
| 0 | AFRICA | lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to |
| 1 | AMERICA | hs use ironic, even requests. s |
| 2 | ASIA | ges. thinly even pinto beans ca |
| 3 | EUROPE | ly final courts cajole furiously final excuse |
| 4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl |
| (5 rows) |
| |
| SELECT * FROM EXT_REGION as r, EXT_NATION as n WHERE n.N_REGIONKEY = r.R_REGIONKEY; |
| r_regionkey | r_name | r_comment | n_nationkey | n_name | n_regionkey | n_comment |
| -------------+---------------------------+---------------------------------------------------------------------------------------------------------------------+-------------+---------------------------+-------------+-------------------------------------------------------------------------------------------------------------------- |
| 4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl | 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d |
| 4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl | 10 | IRAN | 4 | efully alongside of the slyly final dependencies. |
| 4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl | 11 | IRAQ | 4 | nic deposits boost atop the quickly final requests? quickly regula |
| 4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl | 13 | JORDAN | 4 | ic deposits are blithely about the carefully regular pa |
| 4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl | 20 | SAUDI ARABIA | 4 | ts. silent requests haggle. closely express packages sleep across the blithely |
| 0 | AFRICA | lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to | 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai |
| 0 | AFRICA | lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to | 5 | ETHIOPIA | 0 | ven packages wake quickly. regu |
| 2 | ASIA | ges. thinly even pinto beans ca | 8 | INDIA | 2 | ss excuses cajole slyly across the packages. deposits print aroun |
| 2 | ASIA | ges. thinly even pinto beans ca | 9 | INDONESIA | 2 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull |
| 2 | ASIA | ges. thinly even pinto beans ca | 12 | JAPAN | 2 | ously. final, express gifts cajole a |
| 0 | AFRICA | lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to | 14 | KENYA | 0 | pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t |
| 0 | AFRICA | lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to | 15 | MOROCCO | 0 | rns. blithely bold courts among the closely regular packages use furiously bold platelets? |
| 0 | AFRICA | lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to | 16 | MOZAMBIQUE | 0 | s. ironic, unusual asymptotes wake blithely r |
| 2 | ASIA | ges. thinly even pinto beans ca | 18 | CHINA | 2 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos |
| 2 | ASIA | ges. thinly even pinto beans ca | 21 | VIETNAM | 2 | hely enticingly express accounts. even, final |
| 3 | EUROPE | ly final courts cajole furiously final excuse | 6 | FRANCE | 3 | refully final requests. regular, ironi |
| 3 | EUROPE | ly final courts cajole furiously final excuse | 7 | GERMANY | 3 | l platelets. regular accounts x-ray: unusual, regular acco |
| 3 | EUROPE | ly final courts cajole furiously final excuse | 19 | ROMANIA | 3 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account |
| 3 | EUROPE | ly final courts cajole furiously final excuse | 22 | RUSSIA | 3 | requests against the platelets use never according to the quickly regular pint |
| 3 | EUROPE | ly final courts cajole furiously final excuse | 23 | UNITED KINGDOM | 3 | eans boost carefully special requests. accounts are. carefull |
| 1 | AMERICA | hs use ironic, even requests. s | 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon |
| 1 | AMERICA | hs use ironic, even requests. s | 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special |
| 1 | AMERICA | hs use ironic, even requests. s | 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold |
| 1 | AMERICA | hs use ironic, even requests. s | 17 | PERU | 1 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun |
| 1 | AMERICA | hs use ironic, even requests. s | 24 | UNITED STATES | 1 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be |
| (25 rows) |
| |
| -- start_ignore |
| select * from gpfdist_status; |
| x |
| ------------------------------------------------------------------------- |
| Okay, gpfdist version "2.0.1.0 build dev" is running on @hostname@:7070. |
| (1 row) |
| |
| select * from gpfdist_stop; |
| x |
| ------------- |
| stopping... |
| (1 row) |
| |
| select * from gpfdist_status; |
| x |
| ------------------------------------------------------ |
| Error: gpfdist is not running (reason: socket error) |
| Exit: 1 |
| |
| (3 rows) |
| |
| -- end_ignore |
| -- drop tables |
| DROP EXTERNAL TABLE EXT_NATION; |
| DROP EXTERNAL TABLE |
| DROP EXTERNAL TABLE EXT_REGION; |
| DROP EXTERNAL TABLE |
| -- |
| -- gpfdist in csv (mpp-1519, etc) |
| -- |
| CREATE EXTERNAL WEB TABLE gpfdist_csv_start (x text) |
| execute E'((@gpwhich_gpfdist@ -p 7070 -d @abs_srcdir@/data </dev/null >/dev/null 2>&1 &); sleep 2; echo "starting...") ' |
| on SEGMENT 0 |
| FORMAT 'text' (delimiter '|'); |
| CREATE EXTERNAL TABLE |
| -- |
| -- " |
| -- (end the double quote so emacs highlighting works correctly) |
| -- |
| -- start_ignore |
| select * from gpfdist_status; |
| x |
| ------------------------------------------------------ |
| Error: gpfdist is not running (reason: socket error) |
| Exit: 1 |
| |
| (3 rows) |
| |
| select * from gpfdist_stop; |
| x |
| ------------- |
| stopping... |
| (1 row) |
| |
| select * from gpfdist_status; |
| x |
| ------------------------------------------------------ |
| Error: gpfdist is not running (reason: socket error) |
| Exit: 1 |
| |
| (3 rows) |
| |
| select * from gpfdist_csv_start; |
| x |
| ------------- |
| starting... |
| (1 row) |
| |
| select * from gpfdist_status; |
| x |
| ------------------------------------------------------------------------- |
| Okay, gpfdist version "2.0.1.0 build dev" is running on @hostname@:7070. |
| (1 row) |
| |
| -- end_ignore |
| create external table ext_whois ( |
| source_lineno int, |
| domain_name varchar(350), |
| subdomain varchar(150), |
| tld varchar(50), |
| ip_address inet, |
| ip_address_int bigint, |
| reverse_dns varchar(512), |
| reverse_domain varchar(350), |
| registrar varchar(200), |
| registrar_referral varchar(512), |
| whois_server varchar(512), |
| harvest_date timestamp with time zone, |
| created_on timestamp with time zone, |
| update_date timestamp with time zone, |
| expire_date timestamp with time zone, |
| rank int, |
| status char(1), |
| update_status int, |
| nameserver1 varchar(512), |
| nameserver1_domain_name varchar(350), |
| nameserver1_subdomain varchar(150), |
| nameserver1_tld varchar(50), |
| nameserver2 varchar(512), |
| nameserver2_domain_name varchar(350), |
| nameserver2_subdomain varchar(150), |
| nameserver2_tld varchar(50), |
| nameserver3 varchar(512), |
| nameserver3_domain_name varchar(350), |
| nameserver3_subdomain varchar(150), |
| nameserver3_tld varchar(50), |
| nameserver4 varchar(512), |
| nameserver4_domain_name varchar(350), |
| nameserver4_subdomain varchar(150), |
| nameserver4_tld varchar(50), |
| nameserver5 varchar(512), |
| nameserver5_domain_name varchar(350), |
| nameserver5_subdomain varchar(150), |
| nameserver5_tld varchar(50), |
| registrant_name varchar(200), |
| registrant_organization varchar(200), |
| registrant_email varchar(512), |
| registrant_email_domain varchar(350), |
| registrant_email_subdomain varchar(150), |
| registrant_email_tld varchar(50), |
| registrant_phone varchar(50), |
| registrant_fax varchar(50), |
| registrant_addrall varchar(1024), |
| registrant_street1 varchar(200), |
| registrant_street2 varchar(200), |
| registrant_street3 varchar(200), |
| registrant_city varchar(200), |
| registrant_state_province varchar(100), |
| registrant_postal_code varchar(50), |
| registrant_country varchar(100), |
| tech_name varchar(200), |
| tech_organization varchar(200), |
| tech_email varchar(512), |
| tech_email_domain varchar(350), |
| tech_email_subdomain varchar(150), |
| tech_email_tld varchar(50), |
| tech_phone varchar(50), |
| tech_fax varchar(50), |
| tech_addrall varchar(1024), |
| tech_street1 varchar(200), |
| tech_street2 varchar(200), |
| tech_street3 varchar(200), |
| tech_city varchar(200), |
| tech_state_province varchar(100), |
| tech_postal_code varchar(50), |
| tech_country varchar(100), |
| admin_name varchar(200), |
| admin_organization varchar(200), |
| admin_email varchar(512), |
| admin_email_domain varchar(350), |
| admin_email_subdomain varchar(150), |
| admin_email_tld varchar(50), |
| admin_phone varchar(50), |
| admin_fax varchar(50), |
| admin_addrall varchar(1024), |
| admin_street1 varchar(200), |
| admin_street2 varchar(200), |
| admin_street3 varchar(200), |
| admin_city varchar(200), |
| admin_state_province varchar(100), |
| admin_postal_code varchar(50), |
| admin_country varchar(100), |
| rec_path varchar(512), |
| raw_record text |
| ) |
| location ('gpfdist://@hostname@:7070/whois.csv' ) |
| format 'csv' ( quote as '"' header); |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:207: NOTICE: HEADER means that each one of the data files has a header row. |
| CREATE EXTERNAL TABLE |
| -- " |
| -- start_ignore |
| select count(*) from ext_whois; |
| count |
| ------- |
| 23 |
| (1 row) |
| |
| -- end_ignore |
| -- test FILL EMPTY FIELDS |
| create external table ext_fill1(a int, b int, c int) |
| location('gpfdist://@hostname@:7070/missing_fields1.data' ) |
| format 'text' ( delimiter '|' fill missing fields); |
| CREATE EXTERNAL TABLE |
| create external table ext_fill2(a int, b int, c int) |
| location('gpfdist://@hostname@:7070/missing_fields2.data' ) |
| format 'text' ( delimiter '|' fill missing fields); |
| CREATE EXTERNAL TABLE |
| -- also test force not null in addition to fill empty fields |
| create external table ext_fill3_fnn(a text, b text, c text) |
| location('gpfdist://@hostname@:7070/missing_fields1.data' ) |
| format 'csv' ( delimiter '|' fill missing fields force not null c); |
| CREATE EXTERNAL TABLE |
| create external table ext_fill4_fnn(a text, b text, c text) |
| location('gpfdist://@hostname@:7070/missing_fields1.data' ) |
| format 'csv' ( delimiter '|' fill missing fields force not null b,c); |
| CREATE EXTERNAL TABLE |
| select * from ext_fill1 order by a,b,c; -- should pad missing attrs with nulls |
| a | b | c |
| ---+---+--- |
| 1 | 1 | 1 |
| 2 | 2 | |
| 3 | | |
| 4 | 4 | |
| 5 | 5 | 5 |
| (5 rows) |
| |
| -- start_ignore |
| select * from ext_fill2 order by a,b,c; -- should fail due to empty data line |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:233: ERROR: missing data for column "b", found empty data line (seg5 @hostname@:40000 pid=35893) |
| DETAIL: External table ext_fill2, line 3 of gpfdist://@hostname@:7070/missing_fields2.data: "" |
| -- end_ignore |
| select * from ext_fill3_fnn where c is null; -- should be empty |
| a | b | c |
| ---+---+--- |
| (0 rows) |
| |
| select * from ext_fill3_fnn where b is null; -- one row result |
| a | b | c |
| ---+---+--- |
| 3 | | |
| (1 row) |
| |
| select * from ext_fill4_fnn where c is null or b is null; -- should be empty |
| a | b | c |
| ---+---+--- |
| (0 rows) |
| |
| drop external table ext_fill1; |
| DROP EXTERNAL TABLE |
| drop external table ext_fill2; |
| DROP EXTERNAL TABLE |
| drop external table ext_fill3_fnn; |
| DROP EXTERNAL TABLE |
| drop external table ext_fill4_fnn; |
| DROP EXTERNAL TABLE |
| -- test NEWLINE |
| create external table ext_newline1(N_NATIONKEY INT, N_NAME text, N_REGIONKEY INT, N_COMMENT text) |
| location('gpfdist://@hostname@:7070/nation.tbl' ) |
| format 'text' ( delimiter '|' newline 'lf'); |
| CREATE EXTERNAL TABLE |
| create external table ext_newline2(like ext_newline1) |
| location('gpfdist://@hostname@:7070/nation.tbl' ) |
| format 'text' ( delimiter '|' newline 'cr'); |
| CREATE EXTERNAL TABLE |
| create external table ext_willfail(a int) |
| location('gpfdist://@hostname@:7070/nation.tbl' ) |
| format 'text' ( delimiter '|' newline 'blah'); -- should fail with invalid newline. |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:255: ERROR: invalid value for NEWLINE (blah) |
| HINT: valid options are: 'LF', 'CRLF', 'CR' |
| select * from ext_newline1; -- should pass. using the correct linefeed. file has 'lf'. |
| n_nationkey | n_name | n_regionkey | n_comment |
| -------------+----------------+-------------+-------------------------------------------------------------------------------------------------------------------- |
| 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai |
| 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon |
| 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special |
| 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold |
| 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d |
| 5 | ETHIOPIA | 0 | ven packages wake quickly. regu |
| 6 | FRANCE | 3 | refully final requests. regular, ironi |
| 7 | GERMANY | 3 | l platelets. regular accounts x-ray: unusual, regular acco |
| 8 | INDIA | 2 | ss excuses cajole slyly across the packages. deposits print aroun |
| 9 | INDONESIA | 2 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull |
| 10 | IRAN | 4 | efully alongside of the slyly final dependencies. |
| 11 | IRAQ | 4 | nic deposits boost atop the quickly final requests? quickly regula |
| 12 | JAPAN | 2 | ously. final, express gifts cajole a |
| 13 | JORDAN | 4 | ic deposits are blithely about the carefully regular pa |
| 14 | KENYA | 0 | pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t |
| 15 | MOROCCO | 0 | rns. blithely bold courts among the closely regular packages use furiously bold platelets? |
| 16 | MOZAMBIQUE | 0 | s. ironic, unusual asymptotes wake blithely r |
| 17 | PERU | 1 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun |
| 18 | CHINA | 2 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos |
| 19 | ROMANIA | 3 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account |
| 20 | SAUDI ARABIA | 4 | ts. silent requests haggle. closely express packages sleep across the blithely |
| 21 | VIETNAM | 2 | hely enticingly express accounts. even, final |
| 22 | RUSSIA | 3 | requests against the platelets use never according to the quickly regular pint |
| 23 | UNITED KINGDOM | 3 | eans boost carefully special requests. accounts are. carefull |
| 24 | UNITED STATES | 1 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be |
| (25 rows) |
| |
| -- start_ignore |
| select * from ext_newline2; -- should fail. using an incorrect linefeed. file has 'lf'. |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:259: ERROR: extra data after last expected column (seg4 @hostname@:40000 pid=35898) |
| DETAIL: |
| External table ext_newline2, line 1 of gpfdist://@hostname@:7070/nation.tbl: "0|ALGERIA|0| haggle. carefully final deposits detect slyly agai |
| 1|ARGENTINA|1|al foxes promise slyly..." |
| -- end_ignore |
| drop external table ext_newline1; |
| DROP EXTERNAL TABLE |
| drop external table ext_newline2; |
| DROP EXTERNAL TABLE |
| -- MPP-6698 |
| create external table ext_mpp6698(a text) |
| location('gpfdist://@hostname@:7070/mpp6698.data' ) |
| format 'csv' (quote ''''); |
| CREATE EXTERNAL TABLE |
| select * from ext_mpp6698 order by a; -- should ignore the quotes |
| a |
| ---------------------------------------------------- |
| first row |
| second row with embedded single quote ' here |
| third row with another embedded quote at the end ' |
| (3 rows) |
| |
| drop external table ext_mpp6698; |
| DROP EXTERNAL TABLE |
| -- MPP-12839 |
| create external table ext_mpp12839 |
| ( |
| userguid varchar(36), |
| action_time timestamp without time zone, |
| action_type smallint, |
| object_id integer, |
| general_param character varying(8000), |
| plu_id smallint, |
| page_name character varying(2000), |
| browser integer, |
| platform integer, |
| filtering_data character varying(8000), |
| is_new_user boolean, |
| dummy character varying(1) |
| ) |
| location('gpfdist://@hostname@:7070/mpp12839*.data' ) |
| format 'text' (delimiter E'\177' null '' newline 'crlf') |
| segment reject limit 100 rows; |
| CREATE EXTERNAL TABLE |
| select * from ext_mpp12839; -- should not reach reject limit |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:293: NOTICE: Found 6 data formatting errors (6 or more input rows). Rejected related input data. |
| userguid | action_time | action_type | object_id | general_param | plu_id | page_name | browser | platform | filtering_data | is_new_user | dummy |
| ----------+-------------+-------------+-----------+---------------+--------+-----------+---------+----------+----------------+-------------+------- |
| (0 rows) |
| |
| drop external table ext_mpp12839; |
| DROP EXTERNAL TABLE |
| -- -------------------------------------- |
| -- some negative tests |
| -- -------------------------------------- |
| -- |
| -- test for exec child process stderr showing in error message |
| -- |
| create external web table ext_stderr1(a text) execute 'nosuchcommand' ON 0 format 'text'; |
| CREATE EXTERNAL TABLE |
| create external web table ext_stderr2(a text) execute 'cat nosuchfile.txt' ON 0 format 'text'; |
| CREATE EXTERNAL TABLE |
| -- |
| -- bad csv (quote must be a single char) |
| -- |
| create external table bad_whois ( |
| source_lineno int, |
| domain_name varchar(350) |
| ) |
| location ('gpfdist://@hostname@:7070/whois.csv' ) |
| format 'csv' ( header quote as 'ggg'); |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:314: NOTICE: HEADER means that each one of the data files has a header row. |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:314: ERROR: quote must be a single character |
| select count(*) from bad_whois; |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:315: ERROR: relation "bad_whois" does not exist |
| LINE 1: select count(*) from bad_whois; |
| ^ |
| drop external table bad_whois; |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:316: ERROR: table "bad_whois" does not exist |
| -- |
| -- try a bad protocol |
| -- |
| create external table badt2 (x text) |
| location ('bad_protocol://@hostname@@abs_srcdir@/data/no/such/place/badt2.tbl' ) |
| format 'text' (delimiter '|'); |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:323: ERROR: protocol "bad_protocol" does not exist |
| -- |
| -- ALTER |
| -- |
| --create external table ext (a int, x text) |
| --location ('gpfdist://@hostname@:7070@abs_srcdir@/data/no/such/place/badt1.tbl' ) |
| --format 'text'; |
| --alter foreign table ext drop column a; -- should fail (wrong object) |
| --alter table ext drop column a; -- should pass with warning |
| --alter external table ext add column a int; |
| --alter external table ext drop column a; |
| --alter external table ext add column extnewcol int not null; -- should fail (constraints not allowed) |
| --alter external table ext add column extnewcol int; |
| --alter external table ext alter column extnewcol set default 1; -- should fail (unsupported alter type) |
| -- |
| -- TRUNCATE/UPDATE/DELETE/INSERT (INTO RET) |
| -- |
| --truncate ext; |
| --delete from ext; |
| --update ext set x='1' where x='2'; |
| --insert into ext(x) values('a'); |
| --drop table ext; -- should fail (wrong object) |
| --drop foreign table ext; -- should fail (wrong object) |
| --drop external table ext; |
| ---------------------------------------------------------------------- |
| -- CUSTOM PROTOCOLS |
| ---------------------------------------------------------------------- |
| -- DROP EXTERNAL TABLE IF EXISTS ext_w; |
| -- DROP EXTERNAL TABLE IF EXISTS ext_r; |
| -- DROP ROLE IF EXISTS extprotu; |
| -- CREATE OR REPLACE FUNCTION write_to_file() RETURNS integer as '$libdir/gpextprotocol.so', 'demoprot_export' LANGUAGE C STABLE; |
| -- CREATE OR REPLACE FUNCTION read_from_file() RETURNS integer as '$libdir/gpextprotocol.so', 'demoprot_import' LANGUAGE C STABLE; |
| -- DROP PROTOCOL IF EXISTS demoprot; |
| -- CREATE TRUSTED PROTOCOL demoprot (readfunc = 'nosuchfunc'); -- should fail |
| -- CREATE TRUSTED PROTOCOL demoprot (readfunc = 'boolin'); -- should fail |
| -- CREATE TRUSTED PROTOCOL demoprot (readfunc = 'read_from_file', writefunc = 'write_to_file'); -- should succeed |
| -- CREATE PROTOCOL demoprot_untrusted (readfunc = 'read_from_file', writefunc = 'write_to_file'); -- should succeed |
| -- CREATE ROLE extprotu NOSUPERUSER; |
| -- SET SESSION AUTHORIZATION extprotu; |
| -- CREATE WRITABLE EXTERNAL TABLE ext_w(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should fail |
| -- CREATE READABLE EXTERNAL TABLE ext_r(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should fail |
| -- RESET SESSION AUTHORIZATION; |
| -- ALTER PROTOCOL demoprot_untrusted OWNER TO extprotu; -- should fail. protocol is not trusted |
| -- GRANT SELECT ON PROTOCOL demoprot_untrusted TO extprotu; -- should fail. protocol is not trusted |
| -- GRANT SELECT ON PROTOCOL demoprot TO extprotu; |
| -- GRANT INSERT ON PROTOCOL demoprot TO extprotu; |
| -- SET SESSION AUTHORIZATION extprotu; |
| -- CREATE WRITABLE EXTERNAL TABLE ext_w(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should succeed |
| -- CREATE READABLE EXTERNAL TABLE ext_r(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should succeed |
| -- DROP EXTERNAL TABLE IF EXISTS ext_w; |
| -- DROP EXTERNAL TABLE IF EXISTS ext_r; |
| -- RESET SESSION AUTHORIZATION; |
| -- REVOKE INSERT ON PROTOCOL demoprot FROM extprotu; |
| -- SET SESSION AUTHORIZATION extprotu; |
| -- CREATE WRITABLE EXTERNAL TABLE ext_w(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should fail |
| -- CREATE READABLE EXTERNAL TABLE ext_r(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should succeed |
| -- DROP EXTERNAL TABLE ext_r; |
| -- RESET SESSION AUTHORIZATION; |
| -- REVOKE ALL PRIVILEGES ON PROTOCOL demoprot FROM extprotu; |
| -- DROP ROLE IF EXISTS extprotu; |
| -- |
| -- WET tests |
| -- |
| -- |
| -- CREATE (including LIKE, DISTRIBUTED BY) |
| -- |
| -- positive |
| create writable external table wet_pos1(a text, b text) location('gpfdist://@hostname@:7070/wet.out') format 'text'; |
| CREATE EXTERNAL TABLE |
| create writable external table wet_pos2(a text, b text) location('gpfdist://@hostname@:7070/wet.out') format 'text' distributed by(b); |
| CREATE EXTERNAL TABLE |
| create writable external table wet_pos3(like wet_pos2) location('gpfdist://@hostname@:7070/wet.out') format 'text' distributed by(a,b); |
| CREATE EXTERNAL TABLE |
| create writable external table wet_region(like reg_region) location('gpfdist://@hostname@:7070/wet_region.out') format 'text'; |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:405: NOTICE: Table doesn't have 'distributed by' clause, defaulting to distribution columns from LIKE table |
| CREATE EXTERNAL TABLE |
| create readable external table ret_region(like wet_region) location('gpfdist://@hostname@:7070/wet_region.out') format 'text'; |
| CREATE EXTERNAL TABLE |
| -- negative |
| create writable external table wet_neg1(a text, b text) location('gpfdist://@hostname@:7070@abs_srcdir@/badt1.tbl') format 'text'; |
| CREATE EXTERNAL TABLE |
| create writable external table wet_neg1(a text, b text) location('gpfdist://@hostname@:7070/wet.out', 'gpfdist://@hostname@:7070/wet.out') format 'text'; |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:410: ERROR: location uri "gpfdist://@hostname@:7070/wet.out" appears more than once |
| create writable external web table wet_pos5(a text, b text) execute 'some command' on segment 0 format 'text'; |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:411: ERROR: the ON segment syntax for writable external tables is deprecated |
| -- |
| -- SELECT from WET (negative) |
| -- |
| select * from wet_pos1; |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:416: ERROR: External scan error: It is not possible to read from a WRITABLE external table. Create the table as READABLE instead. (CTranslatorDXLToPlStmt.cpp:1041) |
| -- |
| -- WET: export some data with INSERT SELECT, INSERT and COPY. |
| -- |
| -- COPY reg_region FROM STDIN DELIMITER '|'; |
| -- 0|AFRICA|lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to |
| -- 1|AMERICA|hs use ironic, even requests. s |
| -- \. |
| INSERT INTO wet_region SELECT * from reg_region; |
| INSERT 0 0 |
| -- COPY wet_region FROM STDIN DELIMITER '|'; |
| -- 2|ASIA|ges. thinly even pinto beans ca |
| -- 3|EUROPE|ly final courts cajole furiously final excuse |
| -- \. |
| -- INSERT INTO wet_region VALUES(4,'MIDDLE EAST','uickly special'); |
| -- |
| -- Now use RET to see if data was exported correctly. |
| -- NOTE: since we don't bother cleaning up the exported file, it may grow bigger |
| -- in between runs, so we don't check for count(*), and instead check for distinct. |
| -- |
| -- SELECT DISTINCT(R_NAME) FROM ret_region ORDER BY R_NAME; |
| -- start_ignore |
| select * from gpfdist_status; |
| x |
| ------------------------------------------------------------------------- |
| Okay, gpfdist version "2.0.1.0 build dev" is running on @hostname@:7070. |
| (1 row) |
| |
| select * from gpfdist_stop; |
| x |
| ------------- |
| stopping... |
| (1 row) |
| |
| select * from gpfdist_status; |
| x |
| ------------------------------------------------------ |
| Error: gpfdist is not running (reason: socket error) |
| Exit: 1 |
| |
| (3 rows) |
| |
| -- end_ignore |
| -- |
| -- get an error for missing gpfdist |
| -- |
| select count(*) from ext_whois; |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:448: ERROR: connection with gpfdist failed for gpfdist://@hostname@:7070/whois.csv. effective url: http://127.0.0.1:7070/whois.csv. error code = 61 (Connection refused) (seg4 @hostname@:40000 pid=35904) |
| -- |
| -- test CREATE EXTERNAL TABLE privileges |
| -- |
| -- show gp_external_grant_privileges; -- MUST BE OFF for the following tests to work. |
| -- CREATE ROLE exttab1_su SUPERUSER; -- SU with no privs in pg_auth |
| -- CREATE ROLE exttab1_u1 CREATEEXTTABLE(protocol='gpfdist', type='readable'); |
| -- CREATE ROLE exttab1_u2 CREATEEXTTABLE(protocol='gpfdist', type='writable'); |
| -- CREATE ROLE exttab1_u3 CREATEEXTTABLE(protocol='gpfdist') NOCREATEEXTTABLE(protocol='gpfdist', type='readable'); -- fail due to conflict |
| -- SET SESSION AUTHORIZATION exttab1_su; |
| -- create readable external table auth_ext_test1(a int) location ('gpfdist://host:8000/file') format 'text'; |
| -- SET SESSION AUTHORIZATION exttab1_u1; |
| -- create readable external table auth_ext_test2(a int) location ('gpfdist://host:8000/file') format 'text'; |
| -- create writable external table auth_ext_test3(a int) location ('gpfdist://host:8000/file') format 'text'; -- fail |
| -- SET SESSION AUTHORIZATION exttab1_u2; |
| -- create writable external table auth_ext_test3(a int) location ('gpfdist://host:8000/file') format 'text'; |
| -- RESET SESSION AUTHORIZATION; |
| -- ALTER ROLE exttab1_u2 NOCREATEEXTTABLE(protocol='gpfdist', type='writable'); |
| -- SET SESSION AUTHORIZATION exttab1_u2; |
| -- create writable external table auth_ext_test4(a int) location ('gpfdist://host:8000/file') format 'text'; -- fail |
| -- RESET SESSION AUTHORIZATION; |
| -- drop external table auth_ext_test1; |
| -- drop external table auth_ext_test2; |
| -- drop external table auth_ext_test3; |
| -- DROP ROLE exttab1_su; |
| -- DROP ROLE exttab1_u1; |
| -- DROP ROLE exttab1_u2; |
| drop external table ext_whois; |
| DROP EXTERNAL TABLE |
| drop external table gpfdist_csv_start; |
| DROP EXTERNAL TABLE |
| drop external table check_echo; |
| DROP EXTERNAL TABLE |
| drop external table wet_pos1; |
| DROP EXTERNAL TABLE |
| drop external table wet_pos2; |
| DROP EXTERNAL TABLE |
| drop external table wet_pos3; |
| DROP EXTERNAL TABLE |
| drop external table wet_region; |
| DROP EXTERNAL TABLE |
| drop external table ret_region; |
| DROP EXTERNAL TABLE |
| drop table reg_region; |
| DROP TABLE |
| -- Tests for MPP-2513: dropping an external table must result in the deletion |
| -- of the relation pg_exttable row. This was done when using drop external |
| -- table but not drop schema cascade; |
| create schema exttabletest; |
| CREATE SCHEMA |
| CREATE EXTERNAL TABLE exttabletest.EXT_NATION ( N_NATIONKEY INTEGER , |
| N_NAME CHAR(25) , |
| N_REGIONKEY INTEGER , |
| N_COMMENT VARCHAR(152)) |
| location ('gpfdist://host:8000/nation.tbl' ) |
| FORMAT 'text' (delimiter '|'); |
| CREATE EXTERNAL TABLE |
| -- Don't just return the row, as we'll get skew from OID differences between |
| -- runs. |
| select count(*) from pg_catalog.pg_exttable where reloid in (select r.oid from pg_class r where r.relname in ('ext_nation', 'ext_whois', 'gpfdist_status')); |
| count |
| ------- |
| 2 |
| (1 row) |
| |
| drop schema exttabletest cascade; |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:505: NOTICE: drop cascades to external table exttabletest.ext_nation |
| DROP SCHEMA |
| select count(*) from pg_catalog.pg_exttable where reloid in (select r.oid from pg_class r where r.relname in ('ext_nation', 'ext_whois', 'gpfdist_status')); |
| count |
| ------- |
| 1 |
| (1 row) |
| |
| SET gp_foreign_data_access = false; |
| SET |
| CREATE USER nonsuperproto; |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:509: NOTICE: resource queue required -- using default resource queue "pg_default" |
| CREATE ROLE |
| GRANT SELECT ON PROTOCOL pxf TO nonsuperproto; |
| GRANT |
| GRANT INSERT ON PROTOCOL pxf TO nonsuperproto; |
| GRANT |
| REVOKE SELECT ON PROTOCOL pxf FROM nonsuperproto; |
| REVOKE |
| REVOKE INSERT ON PROTOCOL pxf FROM nonsuperproto; |
| REVOKE |
| DROP USER nonsuperproto; |
| DROP ROLE |
| -- start_ignore |
| select * from gpfdist_status; |
| x |
| ------------------------------------------------------ |
| Error: gpfdist is not running (reason: socket error) |
| Exit: 1 |
| |
| (3 rows) |
| |
| select * from gpfdist_start; |
| x |
| ------------- |
| starting... |
| (1 row) |
| |
| select * from gpfdist_status; |
| x |
| ------------------------------------------------------------------------- |
| Okay, gpfdist version "2.0.1.0 build dev" is running on @hostname@:7070. |
| (1 row) |
| |
| -- end_ignore |
| -- Tests for MPP17980: generating artificial CTIDs for external table scans |
| -- This is necessary because the planner currently generates plans that include |
| -- the CTID attribute for external tables. |
| CREATE EXTERNAL TABLE ext_mpp17980 ( id int , id1 int , id2 int) |
| LOCATION ('gpfdist://@hostname@:7070/mpp17980.data') |
| FORMAT 'CSV' ( DELIMITER ',' NULL ' '); |
| CREATE EXTERNAL TABLE |
| CREATE TABLE mpp17980 (id int, date date, amt decimal(10,2)) |
| DISTRIBUTED randomly PARTITION BY RANGE (date) |
| ( START (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE |
| EVERY (INTERVAL '1 month') ); |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_1" for table "mpp17980" |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_2" for table "mpp17980" |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_3" for table "mpp17980" |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_4" for table "mpp17980" |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_5" for table "mpp17980" |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_6" for table "mpp17980" |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_7" for table "mpp17980" |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_8" for table "mpp17980" |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_9" for table "mpp17980" |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_10" for table "mpp17980" |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_11" for table "mpp17980" |
| psql:/tmp/TestExternalTable_TestExternalTableAll.sql:533: NOTICE: CREATE TABLE will create partition "mpp17980_1_prt_12" for table "mpp17980" |
| CREATE TABLE |
| INSERT INTO mpp17980 VALUES ( 1,'2008-02-20',122.11); |
| INSERT 0 1 |
| SELECT COUNT(*) FROM ext_mpp17980 WHERE id IN ( 1 ) ; -- This returns 18 tuples |
| count |
| ------- |
| 18 |
| (1 row) |
| |
| SELECT COUNT(*) FROM ext_mpp17980 WHERE id IN ( SELECT id FROM mpp17980 ) ; -- This should return 18 tuples but returns only 1 |
| count |
| ------- |
| 18 |
| (1 row) |
| |
| SELECT ctid, * FROM ext_mpp17980; |
| ctid | id | id1 | id2 |
| --------+----+-----+----- |
| (0,1) | 1 | 1 | 1 |
| (0,2) | 1 | 2 | 2 |
| (0,3) | 1 | 2 | 3 |
| (0,4) | 1 | 3 | 4 |
| (0,5) | 1 | 2 | 3 |
| (0,6) | 1 | 1 | 1 |
| (0,7) | 1 | 2 | 3 |
| (0,8) | 1 | 2 | 3 |
| (0,9) | 1 | 1 | 1 |
| (0,10) | 1 | 1 | 1 |
| (0,11) | 1 | 2 | 2 |
| (0,12) | 1 | 2 | 3 |
| (0,13) | 1 | 3 | 4 |
| (0,14) | 1 | 2 | 3 |
| (0,15) | 1 | 1 | 1 |
| (0,16) | 1 | 2 | 3 |
| (0,17) | 1 | 2 | 3 |
| (0,18) | 1 | 1 | 1 |
| (18 rows) |
| |
| DROP EXTERNAL TABLE ext_mpp17980; |
| DROP EXTERNAL TABLE |
| DROP TABLE mpp17980; |
| DROP TABLE |
| -- start_ignore |
| select * from gpfdist_status; |
| x |
| ------------------------------------------------------------------------- |
| Okay, gpfdist version "2.0.1.0 build dev" is running on @hostname@:7070. |
| (1 row) |
| |
| select * from gpfdist_stop; |
| x |
| ------------- |
| stopping... |
| (1 row) |
| |
| select * from gpfdist_status; |
| x |
| ------------------------------------------------------ |
| Error: gpfdist is not running (reason: socket error) |
| Exit: 1 |
| |
| (3 rows) |
| |
| -- end_ignore |
| drop external table gpfdist_status; |
| DROP EXTERNAL TABLE |
| drop external table gpfdist_start; |
| DROP EXTERNAL TABLE |
| drop external table gpfdist_stop; |
| DROP EXTERNAL TABLE |
