Your table is missing a "PARTITIONED BY " section. Spark 2.x save the partition information in the TBLPROPERTIES section.
On Sun, Feb 11, 2018 at 10:41 AM, Deepak Sharma <deepakmc...@gmail.com> wrote: > I can see its trying to read the parquet and failing while decompressing > using snappy: > parquet.hadoop.ParquetRecordReader.nextKeyValue(ParquetRecor > dReader.java:201) > > So the table looks good but this needs to be fixed before you can query > the data in hive. > > Thanks > Deepak > > On Sun, Feb 11, 2018 at 1:45 PM, ☼ R Nair (रविशंकर नायर) < > ravishankar.n...@gmail.com> wrote: > >> When I do that , and then do a select, full of errors. I think Hive table >> to read. >> >> select * from mine; >> OK >> SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder". >> SLF4J: Defaulting to no-operation (NOP) logger implementation >> SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for >> further details. >> java.lang.reflect.InvocationTargetException >> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) >> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcce >> ssorImpl.java:62) >> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMe >> thodAccessorImpl.java:43) >> at java.lang.reflect.Method.invoke(Method.java:498) >> at org.xerial.snappy.SnappyLoader.loadNativeLibrary(SnappyLoade >> r.java:317) >> at org.xerial.snappy.SnappyLoader.load(SnappyLoader.java:219) >> at org.xerial.snappy.Snappy.<clinit>(Snappy.java:44) >> at parquet.hadoop.codec.SnappyDecompressor.decompress(SnappyDec >> ompressor.java:62) >> at parquet.hadoop.codec.NonBlockedDecompressorStream.read(NonBl >> ockedDecompressorStream.java:51) >> at java.io.DataInputStream.readFully(DataInputStream.java:195) >> at java.io.DataInputStream.readFully(DataInputStream.java:169) >> at parquet.bytes.BytesInput$StreamBytesInput.toByteArray(BytesI >> nput.java:204) >> at parquet.column.impl.ColumnReaderImpl.readPageV1(ColumnReader >> Impl.java:557) >> at parquet.column.impl.ColumnReaderImpl.access$300(ColumnReader >> Impl.java:57) >> at parquet.column.impl.ColumnReaderImpl$3.visit(ColumnReaderImp >> l.java:516) >> at parquet.column.impl.ColumnReaderImpl$3.visit(ColumnReaderImp >> l.java:513) >> at parquet.column.page.DataPageV1.accept(DataPageV1.java:96) >> at parquet.column.impl.ColumnReaderImpl.readPage(ColumnReaderIm >> pl.java:513) >> at parquet.column.impl.ColumnReaderImpl.checkRead(ColumnReaderI >> mpl.java:505) >> at parquet.column.impl.ColumnReaderImpl.consume(ColumnReaderImp >> l.java:607) >> at parquet.column.impl.ColumnReaderImpl.<init>(ColumnReaderImpl.java:351) >> at parquet.column.impl.ColumnReadStoreImpl.newMemColumnReader(C >> olumnReadStoreImpl.java:66) >> at parquet.column.impl.ColumnReadStoreImpl.getColumnReader(Colu >> mnReadStoreImpl.java:61) >> at parquet.io.RecordReaderImplementation.<init>(RecordReaderImp >> lementation.java:270) >> at parquet.io.MessageColumnIO$1.visit(MessageColumnIO.java:134) >> at parquet.io.MessageColumnIO$1.visit(MessageColumnIO.java:99) >> at parquet.filter2.compat.FilterCompat$NoOpFilter.accept( >> FilterCompat.java:154) >> at parquet.io.MessageColumnIO.getRecordReader(MessageColumnIO.java:99) >> at parquet.hadoop.InternalParquetRecordReader.checkRead(Interna >> lParquetRecordReader.java:137) >> at parquet.hadoop.InternalParquetRecordReader.nextKeyValue(Inte >> rnalParquetRecordReader.java:208) >> at parquet.hadoop.ParquetRecordReader.nextKeyValue(ParquetRecor >> dReader.java:201) >> at org.apache.hadoop.hive.ql.io.parquet.read.ParquetRecordReade >> rWrapper.<init>(ParquetRecordReaderWrapper.java:122) >> at org.apache.hadoop.hive.ql.io.parquet.read.ParquetRecordReade >> rWrapper.<init>(ParquetRecordReaderWrapper.java:85) >> at org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputForma >> t.getRecordReader(MapredParquetInputFormat.java:72) >> at org.apache.hadoop.hive.ql.exec.FetchOperator$FetchInputForma >> tSplit.getRecordReader(FetchOperator.java:673) >> at org.apache.hadoop.hive.ql.exec.FetchOperator.getRecordReader >> (FetchOperator.java:323) >> at org.apache.hadoop.hive.ql.exec.FetchOperator.getNextRow(Fetc >> hOperator.java:445) >> at org.apache.hadoop.hive.ql.exec.FetchOperator.pushRow(FetchOp >> erator.java:414) >> at org.apache.hadoop.hive.ql.exec.FetchTask.fetch(FetchTask.java:140) >> at org.apache.hadoop.hive.ql.Driver.getResults(Driver.java:1670) >> at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriv >> er.java:233) >> at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:165) >> at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:376) >> at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:736) >> at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:681) >> at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:621) >> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) >> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcce >> ssorImpl.java:62) >> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMe >> thodAccessorImpl.java:43) >> at java.lang.reflect.Method.invoke(Method.java:498) >> at org.apache.hadoop.util.RunJar.run(RunJar.java:221) >> at org.apache.hadoop.util.RunJar.main(RunJar.java:136) >> Caused by: java.lang.UnsatisfiedLinkError: no snappyjava in >> java.library.path >> at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1867) >> at java.lang.Runtime.loadLibrary0(Runtime.java:870) >> at java.lang.System.loadLibrary(System.java:1122) >> at org.xerial.snappy.SnappyNativeLoader.loadLibrary(SnappyNativ >> eLoader.java:52) >> ... 52 more >> Exception in thread "main" org.xerial.snappy.SnappyError: >> [FAILED_TO_LOAD_NATIVE_LIBRARY] null >> at org.xerial.snappy.SnappyLoader.load(SnappyLoader.java:229) >> at org.xerial.snappy.Snappy.<clinit>(Snappy.java:44) >> at parquet.hadoop.codec.SnappyDecompressor.decompress(SnappyDec >> ompressor.java:62) >> at parquet.hadoop.codec.NonBlockedDecompressorStream.read(NonBl >> ockedDecompressorStream.java:51) >> at java.io.DataInputStream.readFully(DataInputStream.java:195) >> at java.io.DataInputStream.readFully(DataInputStream.java:169) >> at parquet.bytes.BytesInput$StreamBytesInput.toByteArray(BytesI >> nput.java:204) >> at parquet.column.impl.ColumnReaderImpl.readPageV1(ColumnReader >> Impl.java:557) >> at parquet.column.impl.ColumnReaderImpl.access$300(ColumnReader >> Impl.java:57) >> at parquet.column.impl.ColumnReaderImpl$3.visit(ColumnReaderImp >> l.java:516) >> at parquet.column.impl.ColumnReaderImpl$3.visit(ColumnReaderImp >> l.java:513) >> at parquet.column.page.DataPageV1.accept(DataPageV1.java:96) >> at parquet.column.impl.ColumnReaderImpl.readPage(ColumnReaderIm >> pl.java:513) >> at parquet.column.impl.ColumnReaderImpl.checkRead(ColumnReaderI >> mpl.java:505) >> at parquet.column.impl.ColumnReaderImpl.consume(ColumnReaderImp >> l.java:607) >> at parquet.column.impl.ColumnReaderImpl.<init>(ColumnReaderImpl.java:351) >> at parquet.column.impl.ColumnReadStoreImpl.newMemColumnReader(C >> olumnReadStoreImpl.java:66) >> at parquet.column.impl.ColumnReadStoreImpl.getColumnReader(Colu >> mnReadStoreImpl.java:61) >> at parquet.io.RecordReaderImplementation.<init>(RecordReaderImp >> lementation.java:270) >> at parquet.io.MessageColumnIO$1.visit(MessageColumnIO.java:134) >> at parquet.io.MessageColumnIO$1.visit(MessageColumnIO.java:99) >> at parquet.filter2.compat.FilterCompat$NoOpFilter.accept( >> FilterCompat.java:154) >> at parquet.io.MessageColumnIO.getRecordReader(MessageColumnIO.java:99) >> at parquet.hadoop.InternalParquetRecordReader.checkRead(Interna >> lParquetRecordReader.java:137) >> at parquet.hadoop.InternalParquetRecordReader.nextKeyValue(Inte >> rnalParquetRecordReader.java:208) >> at parquet.hadoop.ParquetRecordReader.nextKeyValue(ParquetRecor >> dReader.java:201) >> at org.apache.hadoop.hive.ql.io.parquet.read.ParquetRecordReade >> rWrapper.<init>(ParquetRecordReaderWrapper.java:122) >> at org.apache.hadoop.hive.ql.io.parquet.read.ParquetRecordReade >> rWrapper.<init>(ParquetRecordReaderWrapper.java:85) >> at org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputForma >> t.getRecordReader(MapredParquetInputFormat.java:72) >> at org.apache.hadoop.hive.ql.exec.FetchOperator$FetchInputForma >> tSplit.getRecordReader(FetchOperator.java:673) >> at org.apache.hadoop.hive.ql.exec.FetchOperator.getRecordReader >> (FetchOperator.java:323) >> at org.apache.hadoop.hive.ql.exec.FetchOperator.getNextRow(Fetc >> hOperator.java:445) >> at org.apache.hadoop.hive.ql.exec.FetchOperator.pushRow(FetchOp >> erator.java:414) >> at org.apache.hadoop.hive.ql.exec.FetchTask.fetch(FetchTask.java:140) >> at org.apache.hadoop.hive.ql.Driver.getResults(Driver.java:1670) >> at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriv >> er.java:233) >> at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:165) >> at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:376) >> at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:736) >> at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:681) >> at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:621) >> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) >> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcce >> ssorImpl.java:62) >> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMe >> thodAccessorImpl.java:43) >> at java.lang.reflect.Method.invoke(Method.java:498) >> at org.apache.hadoop.util.RunJar.run(RunJar.java:221) >> at org.apache.hadoop.util.RunJar.main(RunJar.java:136) >> Feb 11, 2018 3:14:06 AM WARNING: parquet.hadoop.ParquetRecordReader: Can >> not initialize counter due to context is not a instance of >> TaskInputOutputContext, but is org.apache.hadoop.mapreduce.ta >> sk.TaskAttemptContextImpl >> Feb 11, 2018 3:14:06 AM INFO: parquet.hadoop.InternalParquetRecordReader: >> RecordReader initialized will read a total of 36635 records. >> Feb 11, 2018 3:14:06 AM INFO: parquet.hadoop.InternalParquetRecordReader: >> at row 0. reading next block >> Feb 11, 2018 3:14:06 AM INFO: parquet.hadoop.InternalParquetRecordReader: >> block read in memory in 27 ms. row count = 36635 >> >> >> On Sun, Feb 11, 2018 at 3:10 AM, Deepak Sharma <deepakmc...@gmail.com> >> wrote: >> >>> There was a typo: >>> Instead of : >>> alter table mine set locations "hdfs://localhost:8020/user/hi >>> ve/warehouse/mine"; >>> >>> Use : >>> alter table mine set location "hdfs://localhost:8020/user/hi >>> ve/warehouse/mine"; >>> >>> On Sun, Feb 11, 2018 at 1:38 PM, Deepak Sharma <deepakmc...@gmail.com> >>> wrote: >>> >>>> Try this in hive: >>>> alter table mine set locations "hdfs://localhost:8020/user/hi >>>> ve/warehouse/mine"; >>>> >>>> Thanks >>>> Deepak >>>> >>>> On Sun, Feb 11, 2018 at 1:24 PM, ☼ R Nair (रविशंकर नायर) < >>>> ravishankar.n...@gmail.com> wrote: >>>> >>>>> Hi, >>>>> Here you go: >>>>> >>>>> hive> show create table mine; >>>>> OK >>>>> CREATE TABLE `mine`( >>>>> `policyid` int, >>>>> `statecode` string, >>>>> `socialid` string, >>>>> `county` string, >>>>> `eq_site_limit` decimal(10,2), >>>>> `hu_site_limit` decimal(10,2), >>>>> `fl_site_limit` decimal(10,2), >>>>> `fr_site_limit` decimal(10,2), >>>>> `tiv_2014` decimal(10,2), >>>>> `tiv_2015` decimal(10,2), >>>>> `eq_site_deductible` int, >>>>> `hu_site_deductible` int, >>>>> `fl_site_deductible` int, >>>>> `fr_site_deductible` int, >>>>> `latitude` decimal(6,6), >>>>> `longitude` decimal(6,6), >>>>> `line` string, >>>>> `construction` string, >>>>> `point_granularity` int) >>>>> ROW FORMAT SERDE >>>>> 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' >>>>> WITH SERDEPROPERTIES ( >>>>> 'path'='hdfs://localhost:8020/user/hive/warehouse/mine') >>>>> STORED AS INPUTFORMAT >>>>> 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' >>>>> OUTPUTFORMAT >>>>> 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' >>>>> LOCATION >>>>> 'file:/Users/ravishankarnair/spark-warehouse/mine' >>>>> TBLPROPERTIES ( >>>>> 'spark.sql.sources.provider'='parquet', >>>>> 'spark.sql.sources.schema.numParts'='1', >>>>> 'spark.sql.sources.schema.part.0'='{\"type\":\"struct\",\"fi >>>>> elds\":[{\"name\":\"policyid\",\"type\":\"integer\",\"nullab >>>>> le\":true,\"metadata\":{\"name\":\"policyid\",\"scale\":0}}, >>>>> {\"name\":\"statecode\",\"type\":\"string\",\"nullable\":tru >>>>> e,\"metadata\":{\"name\":\"statecode\",\"scale\":0}},{\"name >>>>> \":\"Socialid\",\"type\":\"string\",\"nullable\":true,\"meta >>>>> data\":{\"name\":\"Socialid\",\"scale\":0}},{\"name\":\"coun >>>>> ty\",\"type\":\"string\",\"nullable\":true,\"metadata\":{ >>>>> \"name\":\"county\",\"scale\":0}},{\"name\":\"eq_site_limit\ >>>>> ",\"type\":\"decimal(10,2)\",\"nullable\":true,\"metadata\": >>>>> {\"name\":\"eq_site_limit\",\"scale\":2}},{\"name\":\"hu_ >>>>> site_limit\",\"type\":\"decimal(10,2)\",\"nullable\": >>>>> true,\"metadata\":{\"name\":\"hu_site_limit\",\"scale\":2}}, >>>>> {\"name\":\"fl_site_limit\",\"type\":\"decimal(10,2)\",\" >>>>> nullable\":true,\"metadata\":{\"name\":\"fl_site_limit\",\" >>>>> scale\":2}},{\"name\":\"fr_site_limit\",\"type\":\" >>>>> decimal(10,2)\",\"nullable\":true,\"metadata\":{\"name\":\" >>>>> fr_site_limit\",\"scale\":2}},{\"name\":\"tiv_2014\",\"type\ >>>>> ":\"decimal(10,2)\",\"nullable\":true,\"metadata\":{ >>>>> \"name\":\"tiv_2014\",\"scale\":2}},{\"name\":\"tiv_2015\",\ >>>>> "type\":\"decimal(10,2)\",\"nullable\":true,\"metadata\":{ >>>>> \"name\":\"tiv_2015\",\"scale\":2}},{\"name\":\"eq_site_ >>>>> deductible\",\"type\":\"integer\",\"nullable\":true,\" >>>>> metadata\":{\"name\":\"eq_site_deductible\",\"scale\":0} >>>>> },{\"name\":\"hu_site_deductible\",\"type\":\"intege >>>>> r\",\"nullable\":true,\"metadata\":{\"name\":\"hu_site_ >>>>> deductible\",\"scale\":0}},{\"name\":\"fl_site_deductible\", >>>>> \"type\":\"integer\",\"nullable\":true,\"metadata\":{\"name\ >>>>> ":\"fl_site_deductible\",\"scale\":0}},{\"name\":\"fr_ >>>>> site_deductible\",\"type\":\"integer\",\"nullable\":true,\" >>>>> metadata\":{\"name\":\"fr_site_deductible\",\"scale\":0}},{\ >>>>> "name\":\"latitude\",\"type\":\"decimal(6,6)\",\"nullable\": >>>>> true,\"metadata\":{\"name\":\"latitude\",\"scale\":6}},{\" >>>>> name\":\"longitude\",\"type\":\"decimal(6,6)\",\"nullable\": >>>>> true,\"metadata\":{\"name\":\"longitude\",\"scale\":6}},{\" >>>>> name\":\"line\",\"type\":\"string\",\"nullable\":true,\" >>>>> metadata\":{\"name\":\"line\",\"scale\":0}},{\"name\":\" >>>>> construction\",\"type\":\"string\",\"nullable\":true,\" >>>>> metadata\":{\"name\":\"construction\",\"scale\":0}},{ >>>>> \"name\":\"point_granularity\",\"type\":\"integer\",\"nullab >>>>> le\":true,\"metadata\":{\"name\":\"point_granularity\",\ >>>>> "scale\":0}}]}', >>>>> 'transient_lastDdlTime'='1518335598') >>>>> Time taken: 0.13 seconds, Fetched: 35 row(s) >>>>> >>>>> On Sun, Feb 11, 2018 at 2:36 AM, Shmuel Blitz < >>>>> shmuel.bl...@similarweb.com> wrote: >>>>> >>>>>> Please run the following command, and paste the result: >>>>>> SHOW CREATE TABLE <<TABLE-NAME>> >>>>>> >>>>>> On Sun, Feb 11, 2018 at 7:56 AM, ☼ R Nair (रविशंकर नायर) < >>>>>> ravishankar.n...@gmail.com> wrote: >>>>>> >>>>>>> No, No luck. >>>>>>> >>>>>>> Thanks >>>>>>> >>>>>>> On Sun, Feb 11, 2018 at 12:48 AM, Deepak Sharma < >>>>>>> deepakmc...@gmail.com> wrote: >>>>>>> >>>>>>>> In hive cli: >>>>>>>> msck repair table 《table_name》; >>>>>>>> >>>>>>>> Thanks >>>>>>>> Deepak >>>>>>>> >>>>>>>> On Feb 11, 2018 11:14, "☼ R Nair (रविशंकर नायर)" < >>>>>>>> ravishankar.n...@gmail.com> wrote: >>>>>>>> >>>>>>>>> NO, can you pease explain the command ? Let me try now. >>>>>>>>> >>>>>>>>> Best, >>>>>>>>> >>>>>>>>> On Sun, Feb 11, 2018 at 12:40 AM, Deepak Sharma < >>>>>>>>> deepakmc...@gmail.com> wrote: >>>>>>>>> >>>>>>>>>> I am not sure about the exact issue bjt i see you are partioning >>>>>>>>>> while writing from spark. >>>>>>>>>> Did you tried msck repair on the table before reading it in hive ? >>>>>>>>>> >>>>>>>>>> Thanks >>>>>>>>>> Deepak >>>>>>>>>> >>>>>>>>>> On Feb 11, 2018 11:06, "☼ R Nair (रविशंकर नायर)" < >>>>>>>>>> ravishankar.n...@gmail.com> wrote: >>>>>>>>>> >>>>>>>>>>> All, >>>>>>>>>>> >>>>>>>>>>> Thanks for the inputs. Again I am not successful. I think, we >>>>>>>>>>> need to resolve this, as this is a very common requirement. >>>>>>>>>>> >>>>>>>>>>> Please go through my complete code: >>>>>>>>>>> >>>>>>>>>>> STEP 1: Started Spark shell as spark-shell --master yarn >>>>>>>>>>> >>>>>>>>>>> STEP 2: Flowing code is being given as inout to shark shell >>>>>>>>>>> >>>>>>>>>>> import org.apache.spark.sql.Row >>>>>>>>>>> import org.apache.spark.sql.SparkSession >>>>>>>>>>> val warehouseLocation ="/user/hive/warehouse" >>>>>>>>>>> >>>>>>>>>>> val spark = SparkSession.builder().appName("Spark Hive >>>>>>>>>>> Example").config("spark.sql.warehouse.dir", >>>>>>>>>>> warehouseLocation).enableHiveSupport().getOrCreate() >>>>>>>>>>> >>>>>>>>>>> import org.apache.spark.sql._ >>>>>>>>>>> var passion_df = spark.read. >>>>>>>>>>> format("jdbc"). >>>>>>>>>>> option("url", "jdbc:mysql://localhost:3307/policies"). >>>>>>>>>>> option("driver" ,"com.mysql.jdbc.Driver"). >>>>>>>>>>> option("user", "root"). >>>>>>>>>>> option("password", "root"). >>>>>>>>>>> option("dbtable", "insurancedetails"). >>>>>>>>>>> option("partitionColumn", "policyid"). >>>>>>>>>>> option("lowerBound", "1"). >>>>>>>>>>> option("upperBound", "100000"). >>>>>>>>>>> option("numPartitions", "4"). >>>>>>>>>>> load() >>>>>>>>>>> //Made sure that passion_df is created, as passion_df.show(5) >>>>>>>>>>> shows me correct data. >>>>>>>>>>> passion_df.write.saveAsTable("default.mine") //Default parquet >>>>>>>>>>> >>>>>>>>>>> STEP 3: Went to HIVE. Started HIVE prompt. >>>>>>>>>>> >>>>>>>>>>> hive> show tables; >>>>>>>>>>> OK >>>>>>>>>>> callcentervoicelogs >>>>>>>>>>> mine >>>>>>>>>>> Time taken: 0.035 seconds, Fetched: 2 row(s) >>>>>>>>>>> //As you can see HIVE is showing the table "mine" in default >>>>>>>>>>> schema. >>>>>>>>>>> >>>>>>>>>>> STEP 4: HERE IS THE PROBLEM. >>>>>>>>>>> >>>>>>>>>>> hive> select * from mine; >>>>>>>>>>> OK >>>>>>>>>>> Time taken: 0.354 seconds >>>>>>>>>>> hive> >>>>>>>>>>> //Where is the data ??? >>>>>>>>>>> >>>>>>>>>>> STEP 5: >>>>>>>>>>> >>>>>>>>>>> See the below command on HIVE >>>>>>>>>>> >>>>>>>>>>> describe formatted mine; >>>>>>>>>>> OK >>>>>>>>>>> # col_name data_type comment >>>>>>>>>>> >>>>>>>>>>> policyid int >>>>>>>>>>> statecode string >>>>>>>>>>> socialid string >>>>>>>>>>> county string >>>>>>>>>>> eq_site_limit decimal(10,2) >>>>>>>>>>> hu_site_limit decimal(10,2) >>>>>>>>>>> fl_site_limit decimal(10,2) >>>>>>>>>>> fr_site_limit decimal(10,2) >>>>>>>>>>> tiv_2014 decimal(10,2) >>>>>>>>>>> tiv_2015 decimal(10,2) >>>>>>>>>>> eq_site_deductible int >>>>>>>>>>> hu_site_deductible int >>>>>>>>>>> fl_site_deductible int >>>>>>>>>>> fr_site_deductible int >>>>>>>>>>> latitude decimal(6,6) >>>>>>>>>>> longitude decimal(6,6) >>>>>>>>>>> line string >>>>>>>>>>> construction string >>>>>>>>>>> point_granularity int >>>>>>>>>>> >>>>>>>>>>> # Detailed Table Information >>>>>>>>>>> Database: default >>>>>>>>>>> Owner: ravishankarnair >>>>>>>>>>> CreateTime: Sun Feb 11 00:26:40 EST 2018 >>>>>>>>>>> LastAccessTime: UNKNOWN >>>>>>>>>>> Protect Mode: None >>>>>>>>>>> Retention: 0 >>>>>>>>>>> Location: file:/Users/ravishankarnair/sp >>>>>>>>>>> ark-warehouse/mine >>>>>>>>>>> Table Type: MANAGED_TABLE >>>>>>>>>>> Table Parameters: >>>>>>>>>>> spark.sql.sources.provider parquet >>>>>>>>>>> spark.sql.sources.schema.numParts 1 >>>>>>>>>>> spark.sql.sources.schema.part.0 {\"type\":\"struct\",\"fields\ >>>>>>>>>>> ":[{\"name\":\"policyid\",\"type\":\"integer\",\"nullable\": >>>>>>>>>>> true,\"metadata\":{\"name\":\"policyid\",\"scale\":0}},{\"na >>>>>>>>>>> me\":\"statecode\",\"type\":\"string\",\"nullable\":true,\"m >>>>>>>>>>> etadata\":{\"name\":\"statecode\",\"scale\":0}},{\"name\":\" >>>>>>>>>>> Socialid\",\"type\":\"string\",\"nullable\":true,\"metadata\ >>>>>>>>>>> ":{\"name\":\"Socialid\",\"scale\":0}},{\"name\":\"county\", >>>>>>>>>>> \"type\":\"string\",\"nullable\":true,\"metadata\":{\"name\" >>>>>>>>>>> :\"county\",\"scale\":0}},{\"name\":\"eq_site_limit\",\"type >>>>>>>>>>> \":\"decimal(10,2)\",\"nullable\":true,\"metadata\":{\"name\ >>>>>>>>>>> ":\"eq_site_limit\",\"scale\":2}},{\"name\":\"hu_site_limit\ >>>>>>>>>>> ",\"type\":\"decimal(10,2)\",\"nullable\":true,\"metadata\": >>>>>>>>>>> {\"name\":\"hu_site_limit\",\"scale\":2}},{\"name\":\"fl_sit >>>>>>>>>>> e_limit\",\"type\":\"decimal(10,2)\",\"nullable\":true,\"met >>>>>>>>>>> adata\":{\"name\":\"fl_site_limit\",\"scale\":2}},{\"name\": >>>>>>>>>>> \"fr_site_limit\",\"type\":\"decimal(10,2)\",\"nullable\":tr >>>>>>>>>>> ue,\"metadata\":{\"name\":\"fr_site_limit\",\"scale\":2}},{\ >>>>>>>>>>> "name\":\"tiv_2014\",\"type\":\"decimal(10,2)\",\"nullable\" >>>>>>>>>>> :true,\"metadata\":{\"name\":\"tiv_2014\",\"scale\":2}},{\"n >>>>>>>>>>> ame\":\"tiv_2015\",\"type\":\"decimal(10,2)\",\"nullable\":t >>>>>>>>>>> rue,\"metadata\":{\"name\":\"tiv_2015\",\"scale\":2}},{\"nam >>>>>>>>>>> e\":\"eq_site_deductible\",\"type\":\"integer\",\"nullable\" >>>>>>>>>>> :true,\"metadata\":{\"name\":\"eq_site_deductible\",\"scale\ >>>>>>>>>>> ":0}},{\"name\":\"hu_site_deductible\",\"type\":\"integer\", >>>>>>>>>>> \"nullable\":true,\"metadata\":{\"name\":\"hu_site_deductibl >>>>>>>>>>> e\",\"scale\":0}},{\"name\":\"fl_site_deductible\",\"type\": >>>>>>>>>>> \"integer\",\"nullable\":true,\"metadata\":{\"name\":\"fl_ >>>>>>>>>>> site_deductible\",\"scale\":0}},{\"name\":\"fr_site_ >>>>>>>>>>> deductible\",\"type\":\"integer\",\"nullable\":true,\"metada >>>>>>>>>>> ta\":{\"name\":\"fr_site_deductible\",\"scale\":0}},{\" >>>>>>>>>>> name\":\"latitude\",\"type\":\"decimal(6,6)\",\"nullable\":t >>>>>>>>>>> rue,\"metadata\":{\"name\":\"latitude\",\"scale\":6}},{\"nam >>>>>>>>>>> e\":\"longitude\",\"type\":\"decimal(6,6)\",\"nullable\":tr >>>>>>>>>>> ue,\"metadata\":{\"name\":\"longitude\",\"scale\":6}},{\"nam >>>>>>>>>>> e\":\"line\",\"type\":\"string\",\"nullable\":true,\"metadat >>>>>>>>>>> a\":{\"name\":\"line\",\"scale\":0}},{\"name\":\"constr >>>>>>>>>>> uction\",\"type\":\"string\",\"nullable\":true,\"metadata\": >>>>>>>>>>> {\"name\":\"construction\",\"scale\":0}},{\"name\":\"point_ >>>>>>>>>>> granularity\",\"type\":\"integer\",\"nullable\":true,\" >>>>>>>>>>> metadata\":{\"name\":\"point_granularity\",\"scale\":0}}]} >>>>>>>>>>> transient_lastDdlTime 1518326800 >>>>>>>>>>> >>>>>>>>>>> # Storage Information >>>>>>>>>>> SerDe Library: org.apache.hadoop.hive.ql.io.p >>>>>>>>>>> arquet.serde.ParquetHiveSerDe >>>>>>>>>>> InputFormat: org.apache.hadoop.hive.ql.io.p >>>>>>>>>>> arquet.MapredParquetInputFormat >>>>>>>>>>> OutputFormat: org.apache.hadoop.hive.ql.io.p >>>>>>>>>>> arquet.MapredParquetOutputFormat >>>>>>>>>>> Compressed: No >>>>>>>>>>> Num Buckets: -1 >>>>>>>>>>> Bucket Columns: [] >>>>>>>>>>> Sort Columns: [] >>>>>>>>>>> Storage Desc Params: >>>>>>>>>>> path hdfs://localhost:8020/user/hiv >>>>>>>>>>> e/warehouse/mine >>>>>>>>>>> serialization.format 1 >>>>>>>>>>> Time taken: 0.077 seconds, Fetched: 48 row(s) >>>>>>>>>>> >>>>>>>>>>> Now, I see your advise and support. Whats the issue? Am I doing >>>>>>>>>>> wrong, it it a bug ? I am using Spark 2.2.1, HIVE 1.2.1, HADOOP >>>>>>>>>>> 2.7.3. All >>>>>>>>>>> class path, configuration are set properly. >>>>>>>>>>> >>>>>>>>>>> Best, >>>>>>>>>>> >>>>>>>>>>> Ravion >>>>>>>>>>> >>>>>>>>>>> On Fri, Feb 9, 2018 at 1:29 PM, Nicholas Hakobian < >>>>>>>>>>> nicholas.hakob...@rallyhealth.com> wrote: >>>>>>>>>>> >>>>>>>>>>>> Its possible that the format of your table is not compatible >>>>>>>>>>>> with your version of hive, so Spark saved it in a way such that >>>>>>>>>>>> only Spark >>>>>>>>>>>> can read it. When this happens it prints out a very visible >>>>>>>>>>>> warning letting >>>>>>>>>>>> you know this has happened. >>>>>>>>>>>> >>>>>>>>>>>> We've seen it most frequently when trying to save a parquet >>>>>>>>>>>> file with a column in date format into a Hive table. In older >>>>>>>>>>>> versions of >>>>>>>>>>>> hive, its parquet reader/writer did not support Date formats >>>>>>>>>>>> (among a >>>>>>>>>>>> couple others). >>>>>>>>>>>> >>>>>>>>>>>> Nicholas Szandor Hakobian, Ph.D. >>>>>>>>>>>> Staff Data Scientist >>>>>>>>>>>> Rally Health >>>>>>>>>>>> nicholas.hakob...@rallyhealth.com >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> On Fri, Feb 9, 2018 at 9:59 AM, Prakash Joshi < >>>>>>>>>>>> prakashcjos...@gmail.com> wrote: >>>>>>>>>>>> >>>>>>>>>>>>> Ravi, >>>>>>>>>>>>> >>>>>>>>>>>>> Can you send the result of >>>>>>>>>>>>> Show create table your_table_name >>>>>>>>>>>>> >>>>>>>>>>>>> Thanks >>>>>>>>>>>>> Prakash >>>>>>>>>>>>> >>>>>>>>>>>>> On Feb 9, 2018 8:20 PM, "☼ R Nair (रविशंकर नायर)" < >>>>>>>>>>>>> ravishankar.n...@gmail.com> wrote: >>>>>>>>>>>>> >>>>>>>>>>>>>> All, >>>>>>>>>>>>>> >>>>>>>>>>>>>> It has been three days continuously I am on this issue. Not >>>>>>>>>>>>>> getting any clue. >>>>>>>>>>>>>> >>>>>>>>>>>>>> Environment: Spark 2.2.x, all configurations are correct. >>>>>>>>>>>>>> hive-site.xml is in spark's conf. >>>>>>>>>>>>>> >>>>>>>>>>>>>> 1) Step 1: I created a data frame DF1 reading a csv file. >>>>>>>>>>>>>> >>>>>>>>>>>>>> 2) Did manipulations on DF1. Resulting frame is passion_df. >>>>>>>>>>>>>> >>>>>>>>>>>>>> 3) passion_df.write.format("orc") >>>>>>>>>>>>>> .saveAsTable("sampledb.passion") >>>>>>>>>>>>>> >>>>>>>>>>>>>> 4) The metastore shows the hive table., when I do "show >>>>>>>>>>>>>> tables" in HIVE, I can see table name >>>>>>>>>>>>>> >>>>>>>>>>>>>> 5) I can't select in HIVE, though I can select from SPARK as >>>>>>>>>>>>>> spark.sql("select * from sampledb.passion") >>>>>>>>>>>>>> >>>>>>>>>>>>>> Whats going on here? Please help. Why I am not seeing data >>>>>>>>>>>>>> from HIVE prompt? >>>>>>>>>>>>>> The "describe formatted " command on the table in HIVE shows >>>>>>>>>>>>>> he data is is in default warehouse location ( >>>>>>>>>>>>>> /user/hive/warehouse) since I >>>>>>>>>>>>>> set it. >>>>>>>>>>>>>> >>>>>>>>>>>>>> I am not getting any definite answer anywhere. Many >>>>>>>>>>>>>> suggestions and answers given in Stackoverflow et al.Nothing >>>>>>>>>>>>>> really works. >>>>>>>>>>>>>> >>>>>>>>>>>>>> So asking experts here for some light on this, thanks >>>>>>>>>>>>>> >>>>>>>>>>>>>> Best, >>>>>>>>>>>>>> Ravion >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> -- >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> -- >>>>>>>>> >>>>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> Shmuel Blitz >>>>>> Big Data Developer >>>>>> Email: shmuel.bl...@similarweb.com >>>>>> www.similarweb.com >>>>>> <https://www.facebook.com/SimilarWeb/> >>>>>> <https://www.linkedin.com/company/429838/> >>>>>> <https://twitter.com/similarweb> >>>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> >>>>> >>>> >>>> >>>> -- >>>> Thanks >>>> Deepak >>>> www.bigdatabig.com >>>> www.keosha.net >>>> >>> >>> >>> >>> -- >>> Thanks >>> Deepak >>> www.bigdatabig.com >>> www.keosha.net >>> >> >> >> >> -- >> >> > > > -- > Thanks > Deepak > www.bigdatabig.com > www.keosha.net > -- Shmuel Blitz Big Data Developer Email: shmuel.bl...@similarweb.com www.similarweb.com <https://www.facebook.com/SimilarWeb/> <https://www.linkedin.com/company/429838/> <https://twitter.com/similarweb>
