Hello,
I’m trying out Dremio on my HDP cluster and trying to connect to my Hive tables. I have successfully gotten past some of my permissions issues, connected to HDFS, and some of my Hive Tables. However, a few of my internal Hive tables can’t be accessed and I get errors stating that the Dremio client doesn’t support “insert-only” tables. Is this a shortcoming of Dremio or my configuration?
Thanks,
Nick
Caused by: java.lang.RuntimeException: MetaException(message:Your client does not appear to support insert-only tables. To skip capability checks, please set metastore.client.capability.check to false. This setting can be set globally, or on the client for the current metastore session. Note that this may lead to incorrect results, data loss, undefined behavior, etc. if your client is actually incompatible. You can also specify custom client capabilities via get_table_req API.)
HI @Nick_Lewis
Thank for reaching out. Are these Hive transactional tables. Could you please send me the output of "show create table <table_name> from hive CLI? Also if there is a failed job on the Dremio UI, could you kindly upload the job profile
Share a query Profile
Thanks
@balaji.ramaswamy
Here is my create table statement minus the personal details. I hadn’t noticed that sqoop does create the table as ‘insert-only’ so that certainly makes sense. I don’t get to the point where I can create a job, I get “Error retrieving dataset” when attempting to view the table preview.
+----------------------------------------------------+
| createtab_stmt |
±---------------------------------------------------+
| CREATE TABLE TABLENAME(FIELDS) |
| COMMENT ‘Imported by sqoop on DATE’ |
| ROW FORMAT SERDE |
| ‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’ |
| WITH SERDEPROPERTIES ( |
| ‘field.delim’=’’, |
| ‘line.delim’=’\n’, |
| ‘serialization.format’=’’) |
| STORED AS INPUTFORMAT |
| ‘org.apache.hadoop.mapred.TextInputFormat’ |
| OUTPUTFORMAT |
| ‘org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat’ |
| LOCATION |
| <WAREHOUSE_LOCATION> |
| TBLPROPERTIES ( |
| ‘bucketing_version’=‘2’, |
| ‘transactional’=‘true’, |
| ‘transactional_properties’=‘insert_only’, |
| ‘transient_lastDdlTime’=‘1538059763’) |
±---------------------------------------------------+
Thanks @Nick_Lewis, To get the job profile, run the query again on Dremio, let it fail, then click on jobs page and you should see your select. On the right pane you should see “Download Profile” , click on that and it would download a zip file to your computer. Send us the zip file without extracting it
Alright but as the error happens before this point, I can’t enter any SQL in the editor so that is the majority of this error.
85b6abe8-a2bd-4d6b-91cd-2e5c8bd33b01.zip (4.0 KB)
Hi @Nick_Lewis
I see that your profile has no query. What happens if you do the below?
Logon to the Dremio UI
Click on “New Query”
select * from sys.nodes
Click “preview”
Awesome @Nick_Lewis
Now what happens if you click on your Hive source on the Dremio UI - Then click on the Database your table resides and then you click on the actual table?
Thanks
@balaji.ramaswamy
Now click on jobs and you will see this job failed right at the top. Send us the profile for this failed job. Just the downloaded zip file
Haha I appreciate the help debugging @balaji.ramaswamy, but I believe we are going in a circle here. My prior job profile sent was of this same issue. In the above pictured view I am unable to enter anything in the SQL editor, which leads to any attempts at running a job to fail with the error “invalid query”.
@Nick_Lewis
You do not have to enter anything in UI , when you click the Hive table that you want to see the data you should be able to see the sql “select * from table” and a preview result. Are you getting “Failure While attempting to read metadata”?
Let us also try something else. I assume thge files you are trying to read through Hive Eg: the text files are on HDFS. Can you try and add source-HDFS and see if you are able to directly query the text files?
Thanks
@balaji.ramaswamy
After checking the create statements of some of my tables, the one that failed to read metadata was actually a hive external table that pointed to an HBase table. The access issue makes sense there so I’m not concerned with that.
Meanwhile, my internal hive tables that I sqooped in have a different error when selecting them from my datasets.
I have successfully connected via HDFS and previewed the data for that same table. I can query in that way.
Do this,
Navigate to the page that lists the table name
Click on the little copy icon right next to the table name
Click “New Query”
Type “select * from” and paste the table name in your buffer and hit run
This will for sure produce a profile with an error
Send us the zip file
Thanks
@balaji.ramaswamy
