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. >>>> >>> >>> >> > >
