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> 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> wrote: > >> Actually I don't understand why they have defined TBL_NAME and TBL_TYPE >> as NVARCHAR (this is from Sybase similar to yours) >> >> [image: 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 >> <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> 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> 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 >>>> >>>> >>>> [image: Inline images 1] >>>> >>>> But this is Oracle, a serious database :) >>>> >>>> HTH >>>> >>>> Dr Mich Talebzadeh >>>> >>>> >>>> >>>> LinkedIn * >>>> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw >>>> <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> 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> >>>>> 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> 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 >>>>>>> <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> >>>>>>> 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> 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/jav >>>>>>>>> a/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 >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>> >> >
