spark-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ☼ R Nair (रविशंकर नायर) <ravishankar.n...@gmail.com>
Subject Re: Spark Dataframe and HIVE
Date Sun, 11 Feb 2018 08:55:41 GMT
Hi,
So , is this a bug, or something I need to fix? If its our issue, how can
we fix? Please help.

Best,

On Sun, Feb 11, 2018 at 3:49 AM, Shmuel Blitz <shmuel.blitz@similarweb.com>
wrote:

> 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 <deepakmca05@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.nair@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(Filter
>>> Compat.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(Filter
>>> Compat.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 <deepakmca05@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 <deepakmca05@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.nair@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)\",\"nullab
>>>>>> le\":true,\"metadata\":{\"name\":\"fl_site_limit\",\"sca
>>>>>> le\":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\":\"integer\",\"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,\"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'='1518335598')
>>>>>> Time taken: 0.13 seconds, Fetched: 35 row(s)
>>>>>>
>>>>>> On Sun, Feb 11, 2018 at 2:36 AM, Shmuel Blitz <
>>>>>> shmuel.blitz@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.nair@gmail.com> wrote:
>>>>>>>
>>>>>>>> No, No luck.
>>>>>>>>
>>>>>>>> Thanks
>>>>>>>>
>>>>>>>> On Sun, Feb 11, 2018 at 12:48 AM, Deepak Sharma <
>>>>>>>> deepakmca05@gmail.com> wrote:
>>>>>>>>
>>>>>>>>> In hive cli:
>>>>>>>>> msck repair table 《table_name》;
>>>>>>>>>
>>>>>>>>> Thanks
>>>>>>>>> Deepak
>>>>>>>>>
>>>>>>>>> On Feb 11, 2018 11:14, "☼ R Nair (रविशंकर
नायर)" <
>>>>>>>>> ravishankar.nair@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 <
>>>>>>>>>> deepakmca05@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.nair@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_si
>>>>>>>>>>>> te_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\",\"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.hakobian@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.hakobian@rallyhealth.com
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> On Fri, Feb 9, 2018 at 9:59 AM, Prakash
Joshi <
>>>>>>>>>>>>> prakashcjoshi5@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.nair@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.blitz@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.blitz@similarweb.com
> www.similarweb.com
> <https://www.facebook.com/SimilarWeb/>
> <https://www.linkedin.com/company/429838/>
> <https://twitter.com/similarweb>
>



--

Mime
View raw message