Hello, I wanted to crosspost my SO post on here regarding Hive issues. I wrote it up on that site because it has better formatting, but I will watch the comments here as well. Find all the details here ... View more

Can please let me know why my Hive query is taking such a long time? The query: SELECT DISTINCT res_falg as n FROM my_table took ~75 mins to complete The query: SELECT COUNT(*) FROM my_table WHERE res_flag = 1; took 73 minutes to complete The table is stored on HDFS (replicated on 4 nodes) as a CSV and is only 100MB in size. It has 6 columns, the types are VARCHAR or TINYINT. The column I'm querying has NAs. It is an external table. My hive query is running as a Tez job on YARN using 26 cores and ~120 GB of memory. I am not using LLAP. Any idea what's going on? I'm on HDP 3.0. EDIT: I imported the csv into hdfs using the following command: hdfs dfs -Ddfs.replication=4 -put '/mounted/path/to/file.csv' /dir/file.csv I used these commands to create the table in Hive: CREATE EXTERNAL TABLE my_table ( svcpt_id VARCHAR(50), start_date VARCHAR(8), end_date VARCHAR(8), prem_id VARCHAR(20), res_flag TINYINT, num_prem_id TINYINT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as textfile LOCATION 'hdfs://ncienspk01/APS'; <br>LOAD DATA INPATH '/dir/file.csv' into table my_table; ... View more

@Sandeep Nemuri I recently moved data into HDFS using this command: sqoop import --connect "jdbc:sqlserver://my_server:1433;database=APS_AMI" --username boop -P --table "my_table" --target-dir "hdfs://my_server/test/300M.csv" --split-by "pk" --m 12; Now I'm trying to move into Phoenix and getting this error: Error: java.lang.RuntimeException: org.apache.phoenix.schema.IllegalDataException: java.sql.SQLException: ERROR 201 (22000): Illegal data. ... Caused by: org.apache.phoenix.schema.IllegalDataException: java.sql.SQLException: ERROR 201 (22000): Illegal data. ... Caused by: java.lang.NumberFormatException: For input string: "\N" It looks like Phoenix is having some issues parsing the HDFS csv. Do you know how I can fix it? FOR REFERENCE The Phoenix schema I'm using is: CREATE IMMUTABLE TABLE my_table ( pk VARCHAR(50), id CHAR(10), height_value FLOAT, read_date INTEGER, read_time SMALLINT, units CHAR(1), is_estimate CHAR(1), UTC_offset SMALLINT, Match_flag CHAR(1) CONSTRAINT pk PRIMARY KEY (id)) IMMUTABLE_STORAGE_SCHEME = SINGLE_CELL_ARRAY_WITH_OFFSETS, COLUMN_ENCODED_BYTES = 1; The columns, already loaded in Hive are: | table.pk | table.id | table.height_value | table.read_date | atable.read_time | table.units | table.is_estimate | table.utc_offset | table.match_flag | This is the code I used to move the data into Phoenix HADOOP_CLASSPATH=/usr/hdp/current/hbase-master/lib/hbase-protocol.jar:/usr/hdp/current/hbase-master/conf hadoop jar /usr/hdp/current/phoenix-client/phoenix-5.0.0.3.0.0.0-1634-client.jar org.apache.phoenix.mapreduce.CsvBulkLoadTool --table my_table --input /test/300M.cs ... View more