{"action":"created","issue":{"url":"https://api.github.com/repos/apache/spark/issues/20611","repository_url":"https://api.github.com/repos/apache/spark","labels_url":"https://api.github.com/repos/apache/spark/issues/20611/labels{/name}","comments_url":"https://api.github.com/repos/apache/spark/issues/20611/comments","events_url":"https://api.github.com/repos/apache/spark/issues/20611/events","html_url":"https://github.com/apache/spark/pull/20611","id":297206298,"node_id":"MDExOlB1bGxSZXF1ZXN0MTY5MTkyNjI0","number":20611,"title":"[SPARK-23425][SQL]Support wildcard in HDFS path for load table command","user":{"login":"sujith71955","id":12999161,"node_id":"MDQ6VXNlcjEyOTk5MTYx","avatar_url":"https://avatars1.githubusercontent.com/u/12999161?v=4","gravatar_id":"","url":"https://api.github.com/users/sujith71955","html_url":"https://github.com/sujith71955","followers_url":"https://api.github.com/users/sujith71955/followers","following_url":"https://api.github.com/users/sujith71955/following{/other_user}","gists_url":"https://api.github.com/users/sujith71955/gists{/gist_id}","starred_url":"https://api.github.com/users/sujith71955/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sujith71955/subscriptions","organizations_url":"https://api.github.com/users/sujith71955/orgs","repos_url":"https://api.github.com/users/sujith71955/repos","events_url":"https://api.github.com/users/sujith71955/events{/privacy}","received_events_url":"https://api.github.com/users/sujith71955/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":87,"created_at":"2018-02-14T18:54:16Z","updated_at":"2018-08-15T10:55:36Z","closed_at":null,"author_association":"CONTRIBUTOR","pull_request":{"url":"https://api.github.com/repos/apache/spark/pulls/20611","html_url":"https://github.com/apache/spark/pull/20611","diff_url":"https://github.com/apache/spark/pull/20611.diff","patch_url":"https://github.com/apache/spark/pull/20611.patch"},"body":"## What changes were proposed in this pull request?\r\n**Problem statement**\r\nload data command  with hdfs  file paths consists of  wild card strings like * are not working\r\neg:\r\n\"load data inpath 'hdfs://hacluster/user/ext*  into table t1\"\r\nthrows Analysis exception while executing this query\r\n\r\n![wildcard_issue](https://user-images.githubusercontent.com/12999161/42673744-9f5c0c16-8621-11e8-8d28-cdc41bbe6efe.PNG)\r\n\r\n**Analysis -** \r\nCurrently fs.exists() API which is used for path validation in load command API cannot resolve the path with wild card pattern, To mitigate this problem i am using globStatus() API  another api  which can resolve the paths with hdfs supported wildcards like *,? etc(inline with hive wildcard support).\r\n\r\n**Improvement identified as part of this issue -**\r\nCurrently system wont support wildcard character to be used for folder level path in a local file system.  This PR has handled this scenario, the same globStatus API will unify the validation logic of local and non local file systems, this will ensure the behavior consistency between the hdfs and local file path in  load command.\r\n\r\nwith this improvement user will be able to use a wildcard character in folder level path of a local file system in  load command inline with hive behaviour, in older versions user can use wildcards only in file path of the local file system if they use in folder path system use to give an error by mentioning that not supported.\r\neg: load data local  inpath '/localfilesystem/folder*  into table t1\r\n\r\n## How was this patch tested?\r\na) Manually tested by executing test-cases in HDFS yarn cluster.  Reports is been attached in below section.\r\nb) Existing test-case can verify the impact and functionality  for local file path scenarios\r\nc) A test-case is been added for verifying the functionality when wild card is been used in folder level path of a local file system\r\n## Test Results\r\nNote: all ip's were updated to localhost for security reasons.\r\nHDFS path details\r\n```\r\nvm1:/opt/ficlient # hadoop fs -ls /user/data/sujith1\r\nFound 2 items\r\n-rw-r--r--   3 shahid hadoop       4802 2018-03-26 15:45 /user/data/sujith1/typeddata60.txt\r\n-rw-r--r--   3 shahid hadoop       4883 2018-03-26 15:45 /user/data/sujith1/typeddata61.txt\r\nvm1:/opt/ficlient # hadoop fs -ls /user/data/sujith2\r\nFound 2 items\r\n-rw-r--r--   3 shahid hadoop       4802 2018-03-26 15:45 /user/data/sujith2/typeddata60.txt\r\n-rw-r--r--   3 shahid hadoop       4883 2018-03-26 15:45 /user/data/sujith2/typeddata61.txt\r\n```\r\npositive scenario by specifying complete file path to know about record size\r\n```\r\n0: jdbc:hive2://localhost:22550/default> create table wild_spark (time timestamp, name string, isright boolean, datetoday date, num binary, height double, score float, decimaler decimal(10,0), id tinyint, age int, license bigint, length smallint) row format delimited fields terminated by ',';\r\n+---------+--+\r\n| Result  |\r\n+---------+--+\r\n+---------+--+\r\nNo rows selected (1.217 seconds)\r\n0: jdbc:hive2://localhost:22550/default> load data  inpath '/user/data/sujith1/typeddata60.txt' into table wild_spark;\r\n+---------+--+\r\n| Result  |\r\n+---------+--+\r\n+---------+--+\r\nNo rows selected (4.236 seconds)\r\n0: jdbc:hive2://localhost:22550/default> load data  inpath '/user/data/sujith1/typeddata61.txt' into table wild_spark;\r\n+---------+--+\r\n| Result  |\r\n+---------+--+\r\n+---------+--+\r\nNo rows selected (0.602 seconds)\r\n0: jdbc:hive2://localhost:22550/default> select count(*) from wild_spark;\r\n+-----------+--+\r\n| count(1)  |\r\n+-----------+--+\r\n| 121       |\r\n+-----------+--+\r\n1 row selected (18.529 seconds)\r\n0: jdbc:hive2://localhost:22550/default>\r\n```\r\nWith wild card character in file path\r\n```\r\n0: jdbc:hive2://localhost:22550/default> create table spark_withWildChar (time timestamp, name string, isright boolean, datetoday date, num binary, height double, score float, decimaler decimal(10,0), id tinyint, age int, license bigint, length smallint) row format delimited fields terminated by ',';\r\n+---------+--+\r\n| Result  |\r\n+---------+--+\r\n+---------+--+\r\nNo rows selected (0.409 seconds)\r\n0: jdbc:hive2://localhost:22550/default> load data  inpath '/user/data/sujith1/type*' into table spark_withWildChar;\r\n+---------+--+\r\n| Result  |\r\n+---------+--+\r\n+---------+--+\r\nNo rows selected (1.502 seconds)\r\n0: jdbc:hive2://localhost:22550/default> select count(*) from spark_withWildChar;\r\n+-----------+--+\r\n| count(1)  |\r\n+-----------+--+\r\n| 121       |\r\n+-----------+--+\r\n```\r\nwith ? wild card scenario\r\n```\r\n0: jdbc:hive2://localhost:22550/default> create table spark_withWildChar_DiffChar (time timestamp, name string, isright boolean, datetoday date, num binary, height double, score float, decimaler decimal(10,0), id tinyint, age int, license bigint, length smallint) row format delimited fields terminated by ',';\r\n+---------+--+\r\n| Result  |\r\n+---------+--+\r\n+---------+--+\r\nNo rows selected (0.489 seconds)\r\n0: jdbc:hive2://localhost:22550/default> load data  inpath '/user/data/sujith1/?ypeddata60.txt' into table spark_withWildChar_DiffChar;\r\n+---------+--+\r\n| Result  |\r\n+---------+--+\r\n+---------+--+\r\nNo rows selected (1.152 seconds)\r\n0: jdbc:hive2://localhost:22550/default> load data  inpath '/user/data/sujith1/?ypeddata61.txt' into table spark_withWildChar_DiffChar;\r\n+---------+--+\r\n| Result  |\r\n+---------+--+\r\n+---------+--+\r\nNo rows selected (0.644 seconds)\r\n0: jdbc:hive2://localhost:22550/default> select count(*) from spark_withWildChar_DiffChar;\r\n+-----------+--+\r\n| count(1)  |\r\n+-----------+--+\r\n| 121       |\r\n+-----------+--+\r\n1 row selected (16.078 seconds)\r\n```\r\nwith  folder level wild card scenario\r\n```\r\n0: jdbc:hive2://localhost:22550/default> create table spark_withWildChar_folderlevel (time timestamp, name string, isright boolean, datetoday date, num binary, height double, score float, decimaler decimal(10,0), id tinyint, age int, license bigint, length smallint) row format delimited fields terminated by ',';\r\n+---------+--+\r\n| Result  |\r\n+---------+--+\r\n+---------+--+\r\nNo rows selected (0.489 seconds)\r\n0: jdbc:hive2://localhost:22550/default> load data  inpath '/user/data/suji*/*' into table spark_withWildChar_folderlevel;\r\n+---------+--+\r\n| Result  |\r\n+---------+--+\r\n+---------+--+\r\nNo rows selected (1.152 seconds)\r\n\r\n0: jdbc:hive2://localhost:22550/default> select count(*) from spark_withWildChar_folderlevel;\r\n+-----------+--+\r\n| count(1)  |\r\n+-----------+--+\r\n| 242       |\r\n+-----------+--+\r\n1 row selected (16.078 seconds)\r\n```\r\nNegative scenario invalid path \r\n```\r\n0: jdbc:hive2://localhost:22550/default> load data  inpath '/user/data/sujiinvalid*/*' into  table spark_withWildChar_folder;\r\nError: org.apache.spark.sql.AnalysisException: LOAD DATA input path does not exist: /user/data/sujiinvalid*/*; (state=,code=0)\r\n0: jdbc:hive2://localhost:22550/default>\r\n```\r\nHive Test results- file level\r\n```\r\n0: jdbc:hive2://localhost:21066/> create table hive_withWildChar_files (time timestamp, name string, isright boolean, datetoday date, num binary, height double, score float, decimaler decimal(10,0), id tinyint, age int, license bigint, length smallint) stored as TEXTFILE;\r\nNo rows affected (0.723 seconds)\r\n0: jdbc:hive2://localhost:21066/> load data  inpath '/user/data/sujith1/type*'  into  table hive_withWildChar_files;\r\nINFO  : Loading data to table default.hive_withwildchar_files from hdfs://hacluster/user/sujith1/type*\r\nNo rows affected (0.682 seconds)\r\n0: jdbc:hive2://localhost:21066/> select count(*) from hive_withWildChar_files;\r\n+------+--+\r\n| _c0  |\r\n+------+--+\r\n| 121  |\r\n+------+--+\r\n1 row selected (50.832 seconds)\r\n```\r\nHive Test results- folder level\r\n```\r\n0: jdbc:hive2://localhost:21066/> create table hive_withWildChar_folder (time timestamp, name string, isright boolean, datetoday date, num binary, height double, score float, decimaler decimal(10,0), id tinyint, age int, license bigint, length smallint) stored as TEXTFILE;\r\nNo rows affected (0.459 seconds)\r\n0: jdbc:hive2://localhost:21066/> load data  inpath '/user/data/suji*/*' into table hive_withWildChar_folder;\r\nINFO  : Loading data to table default.hive_withwildchar_folder from hdfs://hacluster/user/data/suji*/*\r\nNo rows affected (0.76 seconds)\r\n0: jdbc:hive2://localhost:21066/> select count(*) from hive_withWildChar_folder;\r\n+------+--+\r\n| _c0  |\r\n+------+--+\r\n| 242  |\r\n+------+--+\r\n1 row selected (46.483 seconds)\r\n```\r\n"},"comment":{"url":"https://api.github.com/repos/apache/spark/issues/comments/413163392","html_url":"https://github.com/apache/spark/pull/20611#issuecomment-413163392","issue_url":"https://api.github.com/repos/apache/spark/issues/20611","id":413163392,"node_id":"MDEyOklzc3VlQ29tbWVudDQxMzE2MzM5Mg==","user":{"login":"AmplabJenkins","id":4031291,"node_id":"MDQ6VXNlcjQwMzEyOTE=","avatar_url":"https://avatars3.githubusercontent.com/u/4031291?v=4","gravatar_id":"","url":"https://api.github.com/users/AmplabJenkins","html_url":"https://github.com/AmplabJenkins","followers_url":"https://api.github.com/users/AmplabJenkins/followers","following_url":"https://api.github.com/users/AmplabJenkins/following{/other_user}","gists_url":"https://api.github.com/users/AmplabJenkins/gists{/gist_id}","starred_url":"https://api.github.com/users/AmplabJenkins/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/AmplabJenkins/subscriptions","organizations_url":"https://api.github.com/users/AmplabJenkins/orgs","repos_url":"https://api.github.com/users/AmplabJenkins/repos","events_url":"https://api.github.com/users/AmplabJenkins/events{/privacy}","received_events_url":"https://api.github.com/users/AmplabJenkins/received_events","type":"User","site_admin":false},"created_at":"2018-08-15T10:55:36Z","updated_at":"2018-08-15T10:55:36Z","author_association":"NONE","body":"Test FAILed.\nRefer to this link for build results (access rights to CI server needed): \nhttps://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/94793/\nTest FAILed."}}
{"actor":{"display_login":"AmplabJenkins"}}