Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Unable to run query select * from <table_name>

SOLVED Go to solution

Unable to run query select * from <table_name>

Explorer

Hi,

 

I see below error,

 

Could some one please help.

 

[dayrhedpru007.enterprisenet.org:21000] > select * from events_census limit 2;

Query: select * from events_census limit 2

ERROR: AnalysisException: Failed to load metadata for table: agf_dev.events_census

CAUSED BY: TableLoadingException: Unrecognized table type for table: agf_dev.events_census

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Unable to run query select * from <table_name>

Master Collaborator
9 REPLIES 9

Re: Unable to run query select * from <table_name>

Master Collaborator

You may be querying an unsupported table type, or the table metadata may be corrupted. We don't support all of Hive's table storage options.

 

If you run "show create table table_name" in Impala, or Hive if it fails in Impala, this will have more information about how the table is stored.

Re: Unable to run query select * from <table_name>

Explorer

Hi Tim,

 

Many thanks for the reply. I am trying to find out root cause of this issue and issue is present only for one table but for other tables.

I am trying to find out to check if the metadata is corrupted or not.

How to check the same, your help is valuable.

Re: Unable to run query select * from <table_name>

Master Collaborator

It would be useful to know how the table was created. Was it created in Impala or another tool like Hive? If you created it in Hive, can you query it from hive?

Re: Unable to run query select * from <table_name>

Explorer

Hi

 

Thanks for the reply. I will get back to you with the information shortly.

Re: Unable to run query select * from <table_name>

Explorer

Hi,

 

Here is the output from hive/impala. Table is created in hive.

 

HIVE
===
hive> SHOW CREATE TABLE tablename;
OK
CREATE TABLE `tablename`(
  `time` int,
  `instrumentationserver` string,
  `cid` string,
  `contentgroup` string,
  `actiontype` string,
  `resourcetype` string,
  `subresourcetype` string,
  `visitor` string,
  `useragent` string,
  `ipaddress` string,
  `measurementmethod` string,
  `justassigned` boolean,
  `robotactivity` string,
  `robotua` string,
  `pageurl` string,
  `referringurl` string,
  `targeturl` string,
  `duration` int,
  `screenresolution` string,
  `colordepth` string,
  `languagecode` string,
  `javaenabled` boolean,
  `cookieenabled` boolean,
  `customtags` array<struct<name:string,value:string>>,
  `partnertags` array<struct<name:string,value:string>>,
  `documenttitle` string,
  `homepage` boolean,
  `connectiontype` string,
  `timezoneoffset` int,
  `browser` string,
  `browserversion` string,
  `os` string,
  `osversion` string,
  `country` string,
  `region` string,
  `city` string,
  `connectionspeed` string,
  `latitude` float,
  `longitude` float,
  `metrocode` int,
  `firstevent` boolean,
  `cookieblocked` boolean,
  `clickpoint` string,
  `primarygroup` string,
  `campaign` string,
  `creative` string,
  `placement` string,
  `result` string,
  `markedresult` boolean,
  `revenue` float,
  `creativegroup` string,
  `streamduration` int,
  `adserver` int,
  `endpoint` int,
  `publisher` string,
  `adnetwork` string,
  `identity` string,
  `sequencenum` int,
  `chapter` int,
  `totalchapternum` int,
  `customtagstr` string,
  `donottrack` boolean,
  `zipcode` string,
  `viewabilitystatus` string,
  `channelasset` string,
  `crashidentifier` boolean,
  `product` string,
  `reportingdatadate` string,
  `viewtimeoffset` int,
  `providerlist` string,
  `suppressprovider` string,
  `forwardflag` boolean,
  `accessmethod` int,
  `encryptionidentifier` boolean,
  `daycd` string,
  `hourcd` int,
  `userlatitude` float,
  `userlongitude` float,
  `assetid` string,
  `advertiseid` string)
PARTITIONED BY (
  `traf_type` string,
  `date` string,
  `hh` string,
  `part` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'hdfs://nameservice1/user/hive/warehouse/dbname.db/tablename'
TBLPROPERTIES (
  'last_modified_by'='hive',
  'last_modified_time'='1445934602',
  'transient_lastDdlTime'='1445934602')
Time taken: 0.205 seconds, Fetched: 98 row(s)
hive>
 
 
IMPALA
=====
 
[impalarunning.server.org:21000] > SHOW CREATE TABLE tablename;
Query: show CREATE TABLE tablename
ERROR: AnalysisException: Failed to load metadata for table: dbname.tablename
CAUSED BY: TableLoadingException: Unrecognized table type for table: dbname.tablename
 
[impalarunning.server.org:21000] >
 
Original table name is replaced by "tablename."

 

Highlighted

Re: Unable to run query select * from <table_name>

Master Collaborator
The table use the ORCFile format, which we don't support in Impala. We
recommend using Parquet, which is supported by Hive, MapReduce, Impala and
many other systems.

Re: Unable to run query select * from <table_name>

Explorer

Hi,

 

Many thanks for the information.

Is there any official web link which informs us about orc format being not supported?

Re: Unable to run query select * from <table_name>

Master Collaborator

Re: Unable to run query select * from <table_name>

Explorer

Thanks for your support. Case is closed now.