Thanks for the detailed info. Sent from my iPhone
> On Dec 4, 2014, at 7:44 AM, Carol McDonald <[email protected]> wrote: > > here is an example using casts and a view, cast , regexp_replace > > use dfsl; > create view `v.clicks_csv` as select cast(columns[0] as int) as id, > cast(columns[1] as int) as session, regexp_replace(cast(columns[2] as > varchar(10)),'"','') as category, regexp_replace(cast(columns[3] as > character(2)),'"','') as state, regexp_replace(cast(columns[4] as > varchar(10)),'"','') as browser, regexp_replace(cast(columns[5] as > character(2)),'"','') as `language`, regexp_replace(cast(columns[6] as > varchar(10)),'"','') as os, cast(columns[7] as int) as user_id, > regexp_replace(cast(columns[8] as varchar(10)),'"','') as prod_cat, > cast(columns[9] as int) as ad_id, regexp_replace(cast(columns[10] as > varchar(10)),'"','') as purch_flag from `clicks.csv` where columns[0] <> 'id'; > > this shows the csv file contents > > >> On Thu, Dec 4, 2014 at 10:39 AM, Carol McDonald <[email protected]> >> wrote: >> why not 3 strings ? >> >> SELECT columns[0] as id1, >> columns[1] as id2, columns[2] as id3, >> FROM dfs.data.`test.csv`; >> >>> On Wed, Dec 3, 2014 at 1:23 PM, Li HM <[email protected]> wrote: >>> using columns[0] worked. >>> >>> Is there a way to case the whole row "*" into a single string? >>> >>> On Wed, Dec 3, 2014 at 2:11 AM, Steven Phillips <[email protected]> >>> wrote: >>> > I haven't really tried squirrel, but it would seem that it doesn't handle >>> > repeated types? >>> > >>> > Instead of select(*), could you try >>> > >>> > select columns[0] from dfs.tmp.`test.csv` >>> > >>> > On Tue, Dec 2, 2014 at 9:46 PM, Li HM <[email protected]> wrote: >>> > >>> >> here is the result of the same query from sqlline >>> >> >>> >> bin/sqlline -u jdbc:drill:zk=`hostname`:2181 >>> >> Drill log directory /var/log/drill does not exist, defaulting to >>> >> /home/lih/opt/drill/log >>> >> sqlline version 1.1.6 >>> >> 0: jdbc:drill:zk=stgace-launcher07.diy.corp.n> select * from >>> >> dfs.tmp.`test.csv`; >>> >> +------------+ >>> >> | columns | >>> >> +------------+ >>> >> | ["1","7664","20140901"] | >>> >> | ["2","17463","20140901"] | >>> >> | ["3","21463","20140901"] | >>> >> | ["4","31976","20140901"] | >>> >> | ["5","15989","20140901"] | >>> >> | ["6","9480","20140901"] | >>> >> | ["7","22419","20140901"] | >>> >> | ["8","15681","20140901"] | >>> >> | ["9","19546","20140901"] | >>> >> +------------+ >>> >> 9 rows selected (207.966 seconds) >>> >> >>> >> On Tue, Dec 2, 2014 at 9:15 PM, mufy <[email protected]> wrote: >>> >> > Hello Li, >>> >> > >>> >> > Are you saying this fails from Squirrel? How about from sqlline? >>> >> > >>> >> > >>> >> > --- >>> >> > Mufeed Usman >>> >> > My LinkedIn <http://www.linkedin.com/pub/mufeed-usman/28/254/400> | My >>> >> > Social Cause <http://www.vision2016.org.in/> | My Blogs : LiveJournal >>> >> > <http://mufeed.livejournal.com> >>> >> > >>> >> > >>> >> > >>> >> > >>> >> > On Wed, Dec 3, 2014 at 9:46 AM, Li HM <[email protected]> wrote: >>> >> > >>> >> >> Thanks so much. This works well if I query a hive table. >>> >> >> >>> >> >> However I get error while querying a plain test file >>> >> >> >>> >> >> select * from dfs.tmp.`test.csv` >>> >> >> <Error> >>> >> >> <Error> >>> >> >> <Error> >>> >> >> <Error> >>> >> >> <Error> >>> >> >> <Error> >>> >> >> <Error> >>> >> >> <Error> >>> >> >> <Error> >>> >> >> >>> >> >> Would would be the syntax to do that? >>> >> >> >>> >> >> >>> >> >> >>> >> >> >>> >> >> >>> >> >> On Tue, Dec 2, 2014 at 7:22 PM, Aditya <[email protected]> >>> >> >> wrote: >>> >> >> > Instead of copying Drill jar to SQuirreL's lib folder, you may want >>> >> >> > to >>> >> >> copy >>> >> >> > it in a separate folder and add it in the extra classpath location >>> >> >> > in >>> >> the >>> >> >> > driver configuration. >>> >> >> > >>> >> >> > This will ensure that SQuirreL's boot classloader does not get >>> >> >> conflicting >>> >> >> > classes. >>> >> >> > >>> >> >> > On Tue, Dec 2, 2014 at 5:20 PM, Li HM <[email protected]> wrote: >>> >> >> > >>> >> >> >> MacOS Yosemite >>> >> >> >> >>> >> >> >> On Tue, Dec 2, 2014 at 5:09 PM, Andries Engelbrecht >>> >> >> >> <[email protected]> wrote: >>> >> >> >> > Have not looked at 0.7 yet, but 0.6r2 worked. >>> >> >> >> > >>> >> >> >> > What OS are you using? >>> >> >> >> > >>> >> >> >> > >>> >> >> >> > On Dec 2, 2014, at 4:11 PM, Li HM <[email protected]> wrote: >>> >> >> >> > >>> >> >> >> >> I am following >>> >> >> >> >>> >> >> >>> >> http://doc.mapr.com/display/MapR/Using+JDBC+to+Access+Apache+Drill+from+SQuirreL >>> >> >> >> >> to set up the jdbc driver with SquirreL. >>> >> >> >> >> >>> >> >> >> >> >>> >> >> >> >> Once I copy over drill-jdbc-all-0.7.0-incubating-SNAPSHOT.jar to >>> >> the >>> >> >> >> >> SQuirreL lib directory, I got the following exception >>> >> >> >> >> >>> >> >> >> >> Exception in thread "main" java.lang.NoSuchMethodError: >>> >> >> >> >> org.apache.log4j.Logger.getRootLogger()Lorg/apache/log4j/Logger; >>> >> >> >> >> at >>> >> >> >> >>> >> org.apache.log4j.BasicConfigurator.configure(BasicConfigurator.java:46) >>> >> >> >> >> at >>> >> >> >> >>> >> >> >>> >> net.sourceforge.squirrel_sql.fw.util.log.Log4jLoggerFactory.<init>(Log4jLoggerFactory.java:149) >>> >> >> >> >> at >>> >> >> >> >>> >> >> >>> >> net.sourceforge.squirrel_sql.fw.util.log.Log4jLoggerFactory.<init>(Log4jLoggerFactory.java:31) >>> >> >> >> >> at >>> >> >> >> >>> >> >> >>> >> net.sourceforge.squirrel_sql.fw.util.log.LoggerController.<clinit>(LoggerController.java:27) >>> >> >> >> >> at >>> >> >> >> >>> >> >> >>> >> net.sourceforge.squirrel_sql.fw.util.StringManager.<clinit>(StringManager.java:38) >>> >> >> >> >> at >>> >> >> >> >>> >> >> >>> >> net.sourceforge.squirrel_sql.fw.util.StringManagerFactory.getStringManager(StringManagerFactory.java:60) >>> >> >> >> >> at >>> >> >> >> net.sourceforge.squirrel_sql.client.Version.<clinit>(Version.java:34) >>> >> >> >> >> at >>> >> >> >> >> net.sourceforge.squirrel_sql.client.Main.main(Main.java:60) >>> >> >> >> >> >>> >> >> >> >> If I replace the log4j.jar with the one from drill, I get >>> >> >> >> >> Exception in thread "main" >>> >> >> >> >> java.lang.IncompatibleClassChangeError: >>> >> >> >> >> Implementing class >>> >> >> >> >> at java.lang.ClassLoader.defineClass1(Native Method) >>> >> >> >> >> at java.lang.ClassLoader.defineClass(ClassLoader.java:800) >>> >> >> >> >> at >>> >> >> >> >>> >> java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142) >>> >> >> >> >> at >>> >> >> >> >> java.net.URLClassLoader.defineClass(URLClassLoader.java:449) >>> >> >> >> >> at java.net.URLClassLoader.access$100(URLClassLoader.java:71) >>> >> >> >> >> at java.net.URLClassLoader$1.run(URLClassLoader.java:361) >>> >> >> >> >> at java.net.URLClassLoader$1.run(URLClassLoader.java:355) >>> >> >> >> >> at java.security.AccessController.doPrivileged(Native Method) >>> >> >> >> >> at java.net.URLClassLoader.findClass(URLClassLoader.java:354) >>> >> >> >> >> at java.lang.ClassLoader.loadClass(ClassLoader.java:425) >>> >> >> >> >> at >>> >> sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308) >>> >> >> >> >> at java.lang.ClassLoader.loadClass(ClassLoader.java:358) >>> >> >> >> >> at >>> >> >> >> >>> >> >> >>> >> net.sourceforge.squirrel_sql.client.SquirrelLoggerFactory.<init>(SquirrelLoggerFactory.java:47) >>> >> >> >> >> at >>> >> net.sourceforge.squirrel_sql.client.Main.startApp(Main.java:80) >>> >> >> >> >> at >>> >> >> >> >> net.sourceforge.squirrel_sql.client.Main.main(Main.java:73) >>> >> >> >> >> >>> >> >> >> >> >>> >> >> >> >> Any suggestions? >>> >> >> >> >> >>> >> >> >> >> Thanks in advance. >>> >> >> >> > >>> >> >> >> >>> >> >> >>> >> >>> > >>> > >>> > >>> > -- >>> > Steven Phillips >>> > Software Engineer >>> > >>> > mapr.com >
