[
https://issues.apache.org/jira/browse/HAWQ-805?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15360760#comment-15360760
]
ASF GitHub Bot commented on HAWQ-805:
-------------------------------------
Github user xunzhang commented on a diff in the pull request:
https://github.com/apache/incubator-hawq/pull/724#discussion_r69403100
--- Diff: src/test/feature/ExternalSource/sql/exttab1.sql.source ---
@@ -0,0 +1,551 @@
+--
+-- 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 optimizer_disable_missing_stats_collection = on;
+CREATE TABLE REG_REGION (R_REGIONKEY INT, R_NAME CHAR(25), R_COMMENT
VARCHAR(152)) DISTRIBUTED BY (R_REGIONKEY);
+
+-- start_ignore
+-- --------------------------------------
+-- check platform
+-- --------------------------------------
+drop external web table if exists check_echo;
+CREATE EXTERNAL WEB TABLE check_echo (x text)
+execute E'(echo gpfdist)'
+on SEGMENT 0
+format 'text';
+select * from check_echo;
+
+
+-- 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 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 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 '|');
+
+-- start_ignore
+select * from gpfdist_stop;
+select * from gpfdist_status;
+select * from gpfdist_start;
+select * from gpfdist_status;
+-- 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 EXT_REGION (LIKE REG_REGION)
+location ('gpfdist://@hostname@:7070/region.tbl' )
+FORMAT 'text' (delimiter '|');
+
+SELECT * FROM EXT_NATION;
+SELECT * FROM EXT_REGION;
+SELECT * FROM EXT_REGION as r, EXT_NATION as n WHERE n.N_REGIONKEY =
r.R_REGIONKEY;
+
+-- start_ignore
+select * from gpfdist_status;
+select * from gpfdist_stop;
+select * from gpfdist_status;
+-- end_ignore
+
+-- drop tables
+DROP EXTERNAL TABLE EXT_NATION;
+DROP EXTERNAL TABLE EXT_REGION;
+
+--
+-- 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 '|');
+--
+-- "
+-- (end the double quote so emacs highlighting works correctly)
+--
+-- start_ignore
+select * from gpfdist_status;
+select * from gpfdist_stop;
+select * from gpfdist_status;
+select * from gpfdist_csv_start;
+select * from gpfdist_status;
+-- 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);
+-- "
+-- start_ignore
+select count(*) from ext_whois;
+-- 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 ext_fill2(a int, b int, c int)
+location('gpfdist://@hostname@:7070/missing_fields2.data' )
+format 'text' ( delimiter '|' fill missing fields);
+
+-- 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 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);
+
+select * from ext_fill1 order by a,b,c; -- should pad missing attrs with
nulls
+-- start_ignore
+select * from ext_fill2 order by a,b,c; -- should fail due to empty data
line
+-- end_ignore
+select * from ext_fill3_fnn where c is null; -- should be empty
+select * from ext_fill3_fnn where b is null; -- one row result
+select * from ext_fill4_fnn where c is null or b is null; -- should be
empty
+
+drop external table ext_fill1;
+drop external table ext_fill2;
+drop external table ext_fill3_fnn;
+drop external table ext_fill4_fnn;
+
+-- 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 ext_newline2(like ext_newline1)
+location('gpfdist://@hostname@:7070/nation.tbl' )
+format 'text' ( delimiter '|' newline 'cr');
+
+create external table ext_willfail(a int)
+location('gpfdist://@hostname@:7070/nation.tbl' )
+format 'text' ( delimiter '|' newline 'blah'); -- should fail with invalid
newline.
+
+select * from ext_newline1; -- should pass. using the correct linefeed.
file has 'lf'.
+-- start_ignore
+select * from ext_newline2; -- should fail. using an incorrect linefeed.
file has 'lf'.
+-- end_ignore
+
+drop external table ext_newline1;
+drop external table ext_newline2;
+
+-- MPP-6698
+create external table ext_mpp6698(a text)
+location('gpfdist://@hostname@:7070/mpp6698.data' )
+format 'csv' (quote '''');
+
+select * from ext_mpp6698 order by a; -- should ignore the quotes
+drop external table ext_mpp6698;
+
+-- 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;
+
+select * from ext_mpp12839; -- should not reach reject limit
+drop external table ext_mpp12839;
+
+-- --------------------------------------
+-- 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 web table ext_stderr2(a text) execute 'cat nosuchfile.txt'
ON 0 format 'text';
+
+--
+-- 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');
+select count(*) from bad_whois;
+drop external table bad_whois;
+
+--
+-- 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 '|');
+
+--
+-- 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 writable external table wet_pos2(a text, b text)
location('gpfdist://@hostname@:7070/wet.out') format 'text' distributed by(b);
+create writable external table wet_pos3(like wet_pos2)
location('gpfdist://@hostname@:7070/wet.out') format 'text' distributed by(a,b);
+create writable external table wet_region(like reg_region)
location('gpfdist://@hostname@:7070/wet_region.out') format 'text';
+create readable external table ret_region(like wet_region)
location('gpfdist://@hostname@:7070/wet_region.out') format 'text';
+
+-- negative
+create writable external table wet_neg1(a text, b text)
location('gpfdist://@hostname@:7070@abs_srcdir@/badt1.tbl') format 'text';
+create writable external table wet_neg1(a text, b text)
location('gpfdist://@hostname@:7070/wet.out',
'gpfdist://@hostname@:7070/wet.out') format 'text';
+create writable external web table wet_pos5(a text, b text) execute 'some
command' on segment 0 format 'text';
+
+--
+-- SELECT from WET (negative)
+--
+select * from wet_pos1;
+
+--
+-- 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;
+
+-- 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');
--- End diff --
The problem is resolved:
https://github.com/apache/incubator-hawq/commit/0bc49a345bb97d3d64980164b0fa9e4601c5953d
> Refactor feature test for exttab1 with new framework
> ----------------------------------------------------
>
> Key: HAWQ-805
> URL: https://issues.apache.org/jira/browse/HAWQ-805
> Project: Apache HAWQ
> Issue Type: Test
> Components: Tests
> Reporter: hongwu
> Assignee: hongwu
>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
