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 <[email protected]> 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 < > [email protected]> 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 <[email protected]> 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 <[email protected]> >>> 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 < >>>> [email protected]> 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 <[email protected]> >>>>> 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 < >>>>>> [email protected]> 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 >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>> >> >
