Thanks Viral, i pass this info onto aour dba's however i dont think the problem is creating the tables, when looking at the logs Hive checks to see if a table called COLUMNS exists and finds a View called COLUMNS instead and therfore does not try to create the table after which any alter statements fail. I think Hive sees the either the default INFORMATION_SCHEMA.COLUMNS view or the SYS.COLUMNS view either way it looks as though these views prevent hive from even attempting to auto create the COLUMNS table. Im not sure where to look in the src to see what sql hive executes to check for the existence of the COLUMNS table to check this however?
With the issues we have faced so far with SQLServer we are going to be using MYSQL temporarily unitll we can iron out these problems. Many thanks, Andy, On 28 March 2011 18:30, 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. >>>>> >>>> >>>> >>> >> >> >
