If this is important enough you should probably patch the source code to get this working and then submit a patch so those changes can be rolled into a future version of hive.
My 2 cents. -Vijay On Mon, Mar 28, 2011 at 10:30 AM, Viral Bajaria <viral.baja...@gmail.com> wrote: > Appan, > Not sure if you meant to email me, but my setup uses MySQL for the > metastore. I ingest/export a lot of data to SqlServer. > Andy, for using SqlServer, the user's schema might be different but is it > owned by DBO ? Maybe instead of using the default msdb for CREATE DATABASE > your admins could use something custom. > I maybe totally wrong about the default msdb option stated above since i > tried a simple CREATE TABLE COLUMNS ( id int, name varhcar(100) ) on my > SqlServer and it worked. If you use sql server management studio you will > notice that COLUMNS is a keyword, I am guessing it has to do with some > server-side settings which does not allow you to use sql server keyword as > your tablename, columnname etc. etc. You can verify the same by doing CREATE > TABLE foo ( year int, day int ) > Thanks, > Viral > > On Mon, Mar 28, 2011 at 9:16 AM, Appan Thirumaligai > <athirumali...@ngmoco.com> wrote: >> >> Andy, >> Here is a short term work around - If your goal is to backup data in SQL >> Server just copy the data out of hadoop and import it into SQL (DTS / >> BCP/SSIS based on which version you are in). Email Viral (I guess he is >> still active in the email list) and I'm sure he is using Hive, Hadoop & SQL >> Server. >> Thanks, >> Appan >> On Mar 25, 2011, at 1:59 AM, shared mailinglists wrote: >> >> Good Morning, >> >> Our DBA's created a new schema associated with the database and then made >> that the default schema for our hive user, unfortunately this resulted in >> the same problem in the logs… >> >> >> >> “Check of existence of COLUMNS returned table type of VIEW” >> >> >> >> … in that Hive still sees the default SQL Server COLUMNS view and >> therefore does create its own COLUMNS table. >> >> Is there any way we can configure Hive to use a different table name or >> any other approaches we could try ? >> >> Many thanks, >> >> Andy. >> >> >> On 24 March 2011 17:23, shared mailinglists >> <shared.mailingli...@gmail.com> wrote: >>> >>> Hi Carl, >>> >>> Many thanks for your suggestions I will put these to our DBAs and see if >>> we can disable the default schema :-) Will post back soon. >>> >>> Cheers & thanks for the rapid replies guys, >>> >>> Andy. >>> >>> On 24 March 2011 17:12, Carl Steinbach <c...@cloudera.com> wrote: >>>> >>>> Hi Andy, >>>> From what I understand SQLServer has the notion of a "default schema" >>>> (usually dbo) which is used to resolve identifiers that are not defined in >>>> a >>>> user's current schema. I think you need to either undefine the default >>>> schema for your metastore user account, or else make it point to the >>>> metastore schema. >>>> Here are some relevant links with more information: >>>> http://msdn.microsoft.com/en-us/library/ms190387.aspx >>>> >>>> http://stackoverflow.com/questions/3806245/sql-server-schema-and-default-schema >>>> >>>> http://dba.fyicenter.com/faq/sql_server_2/Default_Schema_of_Your_Login_Session.html >>>> Hope this helps. >>>> Carl >>>> >>>> On Thu, Mar 24, 2011 at 9:26 AM, Edward Capriolo <edlinuxg...@gmail.com> >>>> wrote: >>>>> >>>>> On Thu, Mar 24, 2011 at 11:36 AM, shared mailinglists >>>>> <shared.mailingli...@gmail.com> wrote: >>>>> > Thanks Bernie, hopefully they will. >>>>> > >>>>> > Were a small Java development team within a predominately MS >>>>> > development >>>>> > house. We’re hopefully introducing new ideas but the normal company >>>>> > politics >>>>> > dictate that we should use SQL Server. That way maintenance, backup, >>>>> > recover >>>>> > etc etc can be handed over to the internal MS db team while freeing >>>>> > us guys >>>>> > to concentrate on better things like Hadoop & Hive :-) I assumed with >>>>> > the DB >>>>> > just being a metadata store that the database wouldn’t be an issue >>>>> > but were >>>>> > struggling a bit:-( >>>>> > >>>>> > On 24 March 2011 15:23, Bennie Schut <bsc...@ebuddy.com> wrote: >>>>> >> >>>>> >> Sorry to become a bit offtopic but how do you get into a situation >>>>> >> where >>>>> >> sqlserver 2005 becomes a requirement for a hive internal meta store? >>>>> >> >>>>> >> I doubt many of the developers of hive will have access to this >>>>> >> database >>>>> >> so I don't expect a lot of response on this. But hopefully someone >>>>> >> can prove >>>>> >> me wrong :) >>>>> >> >>>>> >> Bennie. >>>>> >> >>>>> >> >>>>> >> On 03/24/2011 04:01 PM, shared mailinglists wrote: >>>>> >>> >>>>> >>> Hi Hive users :-) >>>>> >>> >>>>> >>> Does anybody have experience of using Hive with MS SQL Server 2005? >>>>> >>> I’m >>>>> >>> currently stumped with the following issue >>>>> >>> https://issues.apache.org/jira/browse/HIVE-1391 where Hive (or >>>>> >>> DataNucleus?) >>>>> >>> confuses the COLUMNS table it requires internally with that of the >>>>> >>> default >>>>> >>> SQL Server sys.COLUMNS or information_schema.COLUMNS View and >>>>> >>> therefore does >>>>> >>> not automatically create the required metadata table when running >>>>> >>> the Hive >>>>> >>> CLI. >>>>> >>> >>>>> >>> >>>>> >>> Has anybody managed to get Hive to work with SQLServer 2005 or know >>>>> >>> how I >>>>> >>> can configure Hive to use a different table name to COLUMNS ? >>>>> >>> Unfortunately >>>>> >>> we have to use SQL Server and do not have the option to use Derby >>>>> >>> or MySQL >>>>> >>> etc. >>>>> >>> >>>>> >>> Many thanks, >>>>> >>> >>>>> >>> >>>>> >>> Andy. >>>>> >>> >>>>> >> >>>>> > >>>>> > >>>>> >>>>> Let us not forget that M$ SQL Server is very advanced. It has for a >>>>> long time supported many types of things that mysql just plain did >>>>> not. (Did we all forget then mysql 3.X days where we had no >>>>> Transactions or Foreign keys? :) >>>>> >>>>> There was one ticket I closed on it. >>>>> https://issues.apache.org/jira/browse/HIVE-1391 >>>>> >>>>> As far as hive is concerned, m$ SQL server is JPOX/Data Nucleus >>>>> supported so it "should" work. How many deployments exist in the wild >>>>> are unknown. >>>> >>> >> >> > >
