An alternative workaround in the Postgres metastore DB is to replace literal string values 'NULL::character varying' that are were inserted w/o the setting with the actual null-s, in TBLS and SDS tables (and potentially others but I don’t know if there are any).
From: Stephen Sprague <sprag...@gmail.com<mailto:sprag...@gmail.com>> Reply-To: "user@hive.apache.org<mailto:user@hive.apache.org>" <user@hive.apache.org<mailto:user@hive.apache.org>> Date: Friday, August 26, 2016 at 21:08 To: "user@hive.apache.org<mailto:user@hive.apache.org>" <user@hive.apache.org<mailto:user@hive.apache.org>> Subject: Re: hive 2.1.0 + drop view just to cap this discussion... thank you Ashutosh for that link that was very helpful. I did the following based on my reading of it. 1. added the following to hive-site.xml <property> <name>datanucleus.rdbms.initializeColumnInfo</name> <value>NONE</value> </property> this allows one to create views and drop views however it does not allow you to drop views previously created w/o that setting. so... 2. did a show create table on all the views and saved to file. 3. surgically went into the hive metastore and deleted the views from table "TBLS" (but first had to delete from "TABLE_PARAMS" and "TBL_PRIVS" due to ref constraints.) 4. recreated the views as best as possible but given some views are dependent on other views need to make multiple passes That was my workaround anyway. Cheers, Stephen PS. altering the table to 'varchar' did nothing on postgres - thats just a synonym for 'character varying' On Fri, Aug 26, 2016 at 1:40 PM, Ashutosh Chauhan <hashut...@apache.org<mailto:hashut...@apache.org>> wrote: Its a bug in DataNucleus. See discussion on : https://issues.apache.org/jira/browse/HIVE-14322 On Fri, Aug 26, 2016 at 1:34 PM, Mich Talebzadeh <mich.talebza...@gmail.com<mailto:mich.talebza...@gmail.com>> wrote: Actually I don't understand why they have defined TBL_NAME and TBL_TYPE as NVARCHAR (this is from Sybase similar to yours) [Inline images 1] Oracle seems to be correct. And if we look further Use the fixed-length datatype, nchar(n) , and the variable-length datatype, nvarchar(n), for both single-byte and multibyte character sets, such as Japanese. The difference between nchar(n) and char(n) and nvarchar(n) and varchar(n) is that both nchar(n) and nvarchar(n) allocate storage based on n times the number of bytes per character (based on the default character set). char(n) and varchar(n) allocate n bytes of storage. What character set are you using for your server/database? Dr Mich Talebzadeh LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw http://talebzadehmich.wordpress.com Disclaimer: Use it at your own risk.Any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on this email's technical content is explicitly disclaimed. The author will in no case be liable for any monetary damages arising from such loss, damage or destruction. On 26 August 2016 at 21:03, Stephen Sprague <sprag...@gmail.com<mailto:sprag...@gmail.com>> wrote: thanks. what i gotta try is altering the table and changing "character varying(767)" to "varchar(767)" - I think. On Fri, Aug 26, 2016 at 12:59 PM, Mich Talebzadeh <mich.talebza...@gmail.com<mailto:mich.talebza...@gmail.com>> wrote: You don't really want to mess around with the schema. This is what I have in Oracle 12c schema for TBLS. The same as yours [Inline images 1] But this is Oracle, a serious database :) HTH Dr Mich Talebzadeh LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw http://talebzadehmich.wordpress.com Disclaimer: Use it at your own risk.Any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on this email's technical content is explicitly disclaimed. The author will in no case be liable for any monetary damages arising from such loss, damage or destruction. On 26 August 2016 at 20:32, Stephen Sprague <sprag...@gmail.com<mailto:sprag...@gmail.com>> wrote: yeah... so after the hive upgrade scripts ran we have this in pg for table "TABLS" {quote} dwr_prod_2_1_0=> \d "TBLS" Table "public.TBLS" Column | Type | Modifiers --------------------+------------------------+--------------------------------- TBL_ID | bigint | not null CREATE_TIME | bigint | not null DB_ID | bigint | LAST_ACCESS_TIME | bigint | not null OWNER | character varying(767) | default NULL::character varying RETENTION | bigint | not null SD_ID | bigint | TBL_NAME | character varying(128) | default NULL::character varying TBL_TYPE | character varying(128) | default NULL::character varying VIEW_EXPANDED_TEXT | text | VIEW_ORIGINAL_TEXT | text | {quote} wonder if i can perform some surgery here. :o do i feel lucky? On Fri, Aug 26, 2016 at 12:28 PM, Stephen Sprague <sprag...@gmail.com<mailto:sprag...@gmail.com>> wrote: well that doesn't bode well. :( we definitely need to use a remote metastore given this is a prod env with 100's of users. i wasn't able to see anything in the metastore log though so i'm wondering what logger to run to get that? don't think its hive.root.logger. thanks, Stephen. just toggling hive.metastore.try.direct.sql between true or false which seemed like it should influence the metastore access behaviour did not change anything. I guess this is a postgres incompatiblity with jdbc4 (this "character varying" thing.) On Fri, Aug 26, 2016 at 8:55 AM, Mich Talebzadeh <mich.talebza...@gmail.com<mailto:mich.talebza...@gmail.com>> wrote: Sounds like there are a number of issues with Hive metastore on Postgres. There have been a number of reports on this. HTH Dr Mich Talebzadeh LinkedIn https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw http://talebzadehmich.wordpress.com Disclaimer: Use it at your own risk.Any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on this email's technical content is explicitly disclaimed. The author will in no case be liable for any monetary damages arising from such loss, damage or destruction. On 26 August 2016 at 16:43, Stephen Sprague <sprag...@gmail.com<mailto:sprag...@gmail.com>> wrote: thanks Gopal. you're right our metastore is using Postgres. very interesting you were able to intuit that! lemme give your suggestions a try and i'll post back. thanks! Stephen On Fri, Aug 26, 2016 at 8:32 AM, Gopal Vijayaraghavan <gop...@apache.org<mailto:gop...@apache.org>> wrote: > NULL::character%20varying) ... > i want to say this is somehow related to a java version (we're using 8) >but i'm not sure. The "character varying" looks like a lot like a Postgres issue to me (though character varying could be the real term for varchar in another DB). The hive-metastore.log should have the real backtrace. You can try doing set hive.metastore.uris=; set hive.metastore.try.direct.sql=false; (i.e switch to embedded metastore + disable direct sql, in Hive CLI - provided you have all the password stuff for the metastore in the regular hive-site.xml) https://github.com/apache/hive/blob/master/metastore/src/java/org/apache/ha doop/hive/metastore/MetaStoreDirectSql.java#L887<https://github.com/apache/hive/blob/master/metastore/src/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java#L887> Cheers, Gopal
