You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hivemall.apache.org by maropu <gi...@git.apache.org> on 2017/03/08 15:10:53 UTC
[GitHub] incubator-hivemall pull request #61: [HIVEMALL-88][SPARK] Support a function...
GitHub user maropu opened a pull request:
https://github.com/apache/incubator-hivemall/pull/61
[HIVEMALL-88][SPARK] Support a function to flatten nested schemas
## What changes were proposed in this pull request?
Since parquet is first-citizen in Spark, most of users use nested schemas in DF. In some usecases, they need to flatten nested schemas into flat ones to dump data into the other systems that do not support nested schemas, e.g., RDBMS. So, this pr added a conversion function from nested schemas to flat ones.
## What type of PR is it?
Improvement
## What is the Jira issue?
https://issues.apache.org/jira/browse/HIVEMALL-88
## How was this patch tested?
Added tests in `HivemallOpsSuite`.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/maropu/incubator-hivemall HIVEMALL-88
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-hivemall/pull/61.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #61
----
commit 602b79cfdbd1e985eca729932fdca1944f8b471f
Author: Takeshi Yamamuro <ya...@apache.org>
Date: 2017-03-08T15:04:54Z
Support a function to flatten nested schemas
----
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall pull request #61: [HIVEMALL-88][SPARK] Support a function...
Posted by myui <gi...@git.apache.org>.
Github user myui commented on a diff in the pull request:
https://github.com/apache/incubator-hivemall/pull/61#discussion_r105089215
--- Diff: spark/spark-2.1/src/main/scala/org/apache/spark/sql/hive/HivemallOps.scala ---
@@ -805,6 +805,47 @@ final class HivemallOps(df: DataFrame) extends Logging {
JoinTopK(kInt, df.logicalPlan, right.logicalPlan, Inner, Option(joinExprs.expr))(score.named)
}
+ private def doFlatten(schema: StructType, prefix: Option[String] = None) : Seq[Column] = {
+ schema.fields.flatMap { f =>
+ val colName = prefix.map(p => s"$p.${f.name}").getOrElse(f.name)
--- End diff --
??? flatten'ed column names are not a Spark specification. Better to have a ability to use a custom separator.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall issue #61: [HIVEMALL-88][SPARK] Support a function to fla...
Posted by coveralls <gi...@git.apache.org>.
Github user coveralls commented on the issue:
https://github.com/apache/incubator-hivemall/pull/61
[](https://coveralls.io/builds/10518818)
Coverage remained the same at 36.739% when pulling **e7e279b285b15aed9d3b44487a3a1befba51439a on maropu:HIVEMALL-88** into **210b7765b9395e372edbdce925edb48cd180ee48 on apache:master**.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall pull request #61: [HIVEMALL-88][SPARK] Support a function...
Posted by maropu <gi...@git.apache.org>.
Github user maropu commented on a diff in the pull request:
https://github.com/apache/incubator-hivemall/pull/61#discussion_r105093086
--- Diff: spark/spark-2.1/src/main/scala/org/apache/spark/sql/hive/HivemallOps.scala ---
@@ -805,6 +805,47 @@ final class HivemallOps(df: DataFrame) extends Logging {
JoinTopK(kInt, df.logicalPlan, right.logicalPlan, Inner, Option(joinExprs.expr))(score.named)
}
+ private def doFlatten(schema: StructType, prefix: Option[String] = None) : Seq[Column] = {
+ schema.fields.flatMap { f =>
+ val colName = prefix.map(p => s"$p.${f.name}").getOrElse(f.name)
--- End diff --
Actually, we can access this column like this;
```
scala> val df = Seq((1, (1.0, "a"))).toDF()
df: org.apache.spark.sql.DataFrame = [_1: int, _2: struct<_1: double, _2: string>]
scala> val ds1 = df.flatten().select("`_2._1`").show
+-----+
|_2._1|
+-----+
| 1.0|
+-----+
```
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall pull request #61: [HIVEMALL-88][SPARK] Support a function...
Posted by maropu <gi...@git.apache.org>.
Github user maropu commented on a diff in the pull request:
https://github.com/apache/incubator-hivemall/pull/61#discussion_r105089894
--- Diff: spark/spark-2.1/src/main/scala/org/apache/spark/sql/hive/HivemallOps.scala ---
@@ -805,6 +805,47 @@ final class HivemallOps(df: DataFrame) extends Logging {
JoinTopK(kInt, df.logicalPlan, right.logicalPlan, Inner, Option(joinExprs.expr))(score.named)
}
+ private def doFlatten(schema: StructType, prefix: Option[String] = None) : Seq[Column] = {
+ schema.fields.flatMap { f =>
+ val colName = prefix.map(p => s"$p.${f.name}").getOrElse(f.name)
--- End diff --
In Spark, the dot is used as the separator of column names in nested schema.
Currently, Spark users cannot change this separator via configurations.
For example,
```
scala> val ds = Seq((1, (1.0, "a"))).toDS()
ds: org.apache.spark.sql.Dataset[(Int, (Double, String))] = [_1: int, _2: struct<_1: double, _2: string>]
scala> ds.printSchema
root
|-- _1: integer (nullable = false)
|-- _2: struct (nullable = true)
| |-- _1: double (nullable = false)
| |-- _2: string (nullable = true)
scala> ds.select($"_2._2").show
+---+
| _2|
+---+
| a|
+---+
```
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall pull request #61: [HIVEMALL-88][SPARK] Support a function...
Posted by myui <gi...@git.apache.org>.
Github user myui commented on a diff in the pull request:
https://github.com/apache/incubator-hivemall/pull/61#discussion_r105092196
--- Diff: spark/spark-2.1/src/main/scala/org/apache/spark/sql/hive/HivemallOps.scala ---
@@ -805,6 +805,47 @@ final class HivemallOps(df: DataFrame) extends Logging {
JoinTopK(kInt, df.logicalPlan, right.logicalPlan, Inner, Option(joinExprs.expr))(score.named)
}
+ private def doFlatten(schema: StructType, prefix: Option[String] = None) : Seq[Column] = {
+ schema.fields.flatMap { f =>
+ val colName = prefix.map(p => s"$p.${f.name}").getOrElse(f.name)
--- End diff --
`ds.select($"_2._2")` works for nested scheme. So, flatten'ed `ds.flatten` should be accessed without `.`.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall pull request #61: [HIVEMALL-88][SPARK] Support a function...
Posted by maropu <gi...@git.apache.org>.
Github user maropu commented on a diff in the pull request:
https://github.com/apache/incubator-hivemall/pull/61#discussion_r105099394
--- Diff: spark/spark-2.1/src/main/scala/org/apache/spark/sql/hive/HivemallOps.scala ---
@@ -805,6 +805,47 @@ final class HivemallOps(df: DataFrame) extends Logging {
JoinTopK(kInt, df.logicalPlan, right.logicalPlan, Inner, Option(joinExprs.expr))(score.named)
}
+ private def doFlatten(schema: StructType, prefix: Option[String] = None) : Seq[Column] = {
+ schema.fields.flatMap { f =>
+ val colName = prefix.map(p => s"$p.${f.name}").getOrElse(f.name)
--- End diff --
@myui How about the latest fix? As you suggested, I added an option for separator.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall pull request #61: [HIVEMALL-88][SPARK] Support a function...
Posted by maropu <gi...@git.apache.org>.
Github user maropu commented on a diff in the pull request:
https://github.com/apache/incubator-hivemall/pull/61#discussion_r105049868
--- Diff: docs/gitbook/SUMMARY.md ---
@@ -163,6 +163,7 @@
* [Generic features](spark/misc/misc.md)
* [Top-k Join processing](spark/misc/topk_join.md)
+ * [Ohter utility functions](spark/misc/functions.md)
--- End diff --
Fixed and Thanks!
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall pull request #61: [HIVEMALL-88][SPARK] Support a function...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/incubator-hivemall/pull/61
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall pull request #61: [HIVEMALL-88][SPARK] Support a function...
Posted by myui <gi...@git.apache.org>.
Github user myui commented on a diff in the pull request:
https://github.com/apache/incubator-hivemall/pull/61#discussion_r105100218
--- Diff: spark/spark-2.1/src/main/scala/org/apache/spark/sql/hive/HivemallOps.scala ---
@@ -805,6 +805,47 @@ final class HivemallOps(df: DataFrame) extends Logging {
JoinTopK(kInt, df.logicalPlan, right.logicalPlan, Inner, Option(joinExprs.expr))(score.named)
}
+ private def doFlatten(schema: StructType, prefix: Option[String] = None) : Seq[Column] = {
+ schema.fields.flatMap { f =>
+ val colName = prefix.map(p => s"$p.${f.name}").getOrElse(f.name)
--- End diff --
@maropu \U0001f44d LGTM. Could you merge and close JIRA ticket as FIXED?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall pull request #61: [HIVEMALL-88][SPARK] Support a function...
Posted by maropu <gi...@git.apache.org>.
Github user maropu commented on a diff in the pull request:
https://github.com/apache/incubator-hivemall/pull/61#discussion_r105090944
--- Diff: spark/spark-2.1/src/main/scala/org/apache/spark/sql/hive/HivemallOps.scala ---
@@ -805,6 +805,47 @@ final class HivemallOps(df: DataFrame) extends Logging {
JoinTopK(kInt, df.logicalPlan, right.logicalPlan, Inner, Option(joinExprs.expr))(score.named)
}
+ private def doFlatten(schema: StructType, prefix: Option[String] = None) : Seq[Column] = {
+ schema.fields.flatMap { f =>
+ val colName = prefix.map(p => s"$p.${f.name}").getOrElse(f.name)
--- End diff --
Ah, I found an issue;
```
scala> val df = Seq((1, (1.0, "a"))).toDF()
df: org.apache.spark.sql.DataFrame = [_1: int, _2: struct<_1: double, _2: string>]
scala> val ds1 = df.flatten().select("_2._1")
org.apache.spark.sql.AnalysisException: cannot resolve '`_2._1`' given input columns: [_1, _2._1, _2._2];;
'Project ['_2._1]
+- Project [_1#67 AS _1#73, _2#68._1 AS _2._1#74, _2#68._2 AS _2._2#75]
+- LocalRelation [_1#67, _2#68]
at org.apache.spark.sql.catalyst.analysis.package$AnalysisErrorAt.failAnalysis(package.scala:42)
at org.apache.spark.sql.catalyst.analysis.CheckAnalysis$$anonfun$checkAnalysis$1$$anonfun$apply$2.applyOrElse(CheckAnalysis.scala:75)
at org.apache.spark.sql.catalyst.analysis.CheckAnalysis$$anonfun$checkAnalysis$1$$anonfun$apply$2.applyOrElse(CheckAnalysis.scala:72)
at org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$transformUp$1.apply(TreeNode.scala:289)
```
So, I'll reconsider this and please give me a sec. Thanks.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall issue #61: [HIVEMALL-88][SPARK] Support a function to fla...
Posted by coveralls <gi...@git.apache.org>.
Github user coveralls commented on the issue:
https://github.com/apache/incubator-hivemall/pull/61
[](https://coveralls.io/builds/10518994)
Coverage remained the same at 36.739% when pulling **e7e279b285b15aed9d3b44487a3a1befba51439a on maropu:HIVEMALL-88** into **210b7765b9395e372edbdce925edb48cd180ee48 on apache:master**.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall pull request #61: [HIVEMALL-88][SPARK] Support a function...
Posted by myui <gi...@git.apache.org>.
Github user myui commented on a diff in the pull request:
https://github.com/apache/incubator-hivemall/pull/61#discussion_r105071319
--- Diff: spark/spark-2.1/src/main/scala/org/apache/spark/sql/hive/HivemallOps.scala ---
@@ -805,6 +805,47 @@ final class HivemallOps(df: DataFrame) extends Logging {
JoinTopK(kInt, df.logicalPlan, right.logicalPlan, Inner, Option(joinExprs.expr))(score.named)
}
+ private def doFlatten(schema: StructType, prefix: Option[String] = None) : Seq[Column] = {
+ schema.fields.flatMap { f =>
+ val colName = prefix.map(p => s"$p.${f.name}").getOrElse(f.name)
--- End diff --
Dot `.` is a special symbol in SQL. So, better to change the default separator (e.g., `$` ) and have an option to choose the separator. `news20.train` means train table in news20 database.
SQL identifiers are depending on RDBMSs but generally includes `[0-9,a-z,A-Z$_]`.
https://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html
https://msdn.microsoft.com/en-us/library/ms175874.aspx
https://dev.mysql.com/doc/refman/5.7/en/identifiers.html
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall issue #61: [HIVEMALL-88][SPARK] Support a function to fla...
Posted by coveralls <gi...@git.apache.org>.
Github user coveralls commented on the issue:
https://github.com/apache/incubator-hivemall/pull/61
[](https://coveralls.io/builds/10503982)
Coverage remained the same at 36.739% when pulling **880bed97b48889ee8bc5a51807a97c2fdc032bee on maropu:HIVEMALL-88** into **210b7765b9395e372edbdce925edb48cd180ee48 on apache:master**.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall issue #61: [HIVEMALL-88][SPARK] Support a function to fla...
Posted by coveralls <gi...@git.apache.org>.
Github user coveralls commented on the issue:
https://github.com/apache/incubator-hivemall/pull/61
[](https://coveralls.io/builds/10518824)
Coverage increased (+0.3%) to 37.03% when pulling **e7e279b285b15aed9d3b44487a3a1befba51439a on maropu:HIVEMALL-88** into **210b7765b9395e372edbdce925edb48cd180ee48 on apache:master**.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall pull request #61: [HIVEMALL-88][SPARK] Support a function...
Posted by maropu <gi...@git.apache.org>.
Github user maropu commented on a diff in the pull request:
https://github.com/apache/incubator-hivemall/pull/61#discussion_r105102936
--- Diff: spark/spark-2.1/src/main/scala/org/apache/spark/sql/hive/HivemallOps.scala ---
@@ -805,6 +805,47 @@ final class HivemallOps(df: DataFrame) extends Logging {
JoinTopK(kInt, df.logicalPlan, right.logicalPlan, Inner, Option(joinExprs.expr))(score.named)
}
+ private def doFlatten(schema: StructType, prefix: Option[String] = None) : Seq[Column] = {
+ schema.fields.flatMap { f =>
+ val colName = prefix.map(p => s"$p.${f.name}").getOrElse(f.name)
--- End diff --
okay, thanks. I'll merge soon.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall issue #61: [HIVEMALL-88][SPARK] Support a function to fla...
Posted by maropu <gi...@git.apache.org>.
Github user maropu commented on the issue:
https://github.com/apache/incubator-hivemall/pull/61
Merged to master.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall pull request #61: [HIVEMALL-88][SPARK] Support a function...
Posted by maropu <gi...@git.apache.org>.
Github user maropu commented on a diff in the pull request:
https://github.com/apache/incubator-hivemall/pull/61#discussion_r105088535
--- Diff: spark/spark-2.1/src/main/scala/org/apache/spark/sql/hive/HivemallOps.scala ---
@@ -805,6 +805,47 @@ final class HivemallOps(df: DataFrame) extends Logging {
JoinTopK(kInt, df.logicalPlan, right.logicalPlan, Inner, Option(joinExprs.expr))(score.named)
}
+ private def doFlatten(schema: StructType, prefix: Option[String] = None) : Seq[Column] = {
+ schema.fields.flatMap { f =>
+ val colName = prefix.map(p => s"$p.${f.name}").getOrElse(f.name)
--- End diff --
I know, but this is a Spark-local specification. So, the change you suggested make `doFlatten` fail.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall pull request #61: [HIVEMALL-88][SPARK] Support a function...
Posted by takuti <gi...@git.apache.org>.
Github user takuti commented on a diff in the pull request:
https://github.com/apache/incubator-hivemall/pull/61#discussion_r104953528
--- Diff: docs/gitbook/SUMMARY.md ---
@@ -163,6 +163,7 @@
* [Generic features](spark/misc/misc.md)
* [Top-k Join processing](spark/misc/topk_join.md)
+ * [Ohter utility functions](spark/misc/functions.md)
--- End diff --
typo~
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall pull request #61: [HIVEMALL-88][SPARK] Support a function...
Posted by maropu <gi...@git.apache.org>.
Github user maropu commented on a diff in the pull request:
https://github.com/apache/incubator-hivemall/pull/61#discussion_r105090100
--- Diff: spark/spark-2.1/src/main/scala/org/apache/spark/sql/hive/HivemallOps.scala ---
@@ -805,6 +805,47 @@ final class HivemallOps(df: DataFrame) extends Logging {
JoinTopK(kInt, df.logicalPlan, right.logicalPlan, Inner, Option(joinExprs.expr))(score.named)
}
+ private def doFlatten(schema: StructType, prefix: Option[String] = None) : Seq[Column] = {
+ schema.fields.flatMap { f =>
+ val colName = prefix.map(p => s"$p.${f.name}").getOrElse(f.name)
--- End diff --
So, the dot is more natural for Spark users.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall issue #61: [HIVEMALL-88][SPARK] Support a function to fla...
Posted by coveralls <gi...@git.apache.org>.
Github user coveralls commented on the issue:
https://github.com/apache/incubator-hivemall/pull/61
[](https://coveralls.io/builds/10518937)
Coverage increased (+0.3%) to 37.03% when pulling **e7e279b285b15aed9d3b44487a3a1befba51439a on maropu:HIVEMALL-88** into **210b7765b9395e372edbdce925edb48cd180ee48 on apache:master**.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] incubator-hivemall issue #61: [HIVEMALL-88][SPARK] Support a function to fla...
Posted by coveralls <gi...@git.apache.org>.
Github user coveralls commented on the issue:
https://github.com/apache/incubator-hivemall/pull/61
[](https://coveralls.io/builds/10509375)
Coverage remained the same at 36.739% when pulling **e7e279b285b15aed9d3b44487a3a1befba51439a on maropu:HIVEMALL-88** into **210b7765b9395e372edbdce925edb48cd180ee48 on apache:master**.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---