You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@samza.apache.org by at...@apache.org on 2018/12/25 05:00:52 UTC
samza-hello-samza git commit: Adding Samza SQL Examples
Repository: samza-hello-samza
Updated Branches:
refs/heads/master 0e146873d -> 53ba8e3ec
Adding Samza SQL Examples
Author: Shenoda Guirguis <sg...@linkedin.com>
Reviewers: atoomula
Closes #46 from shenodaguirguis/sqlexamples
Project: http://git-wip-us.apache.org/repos/asf/samza-hello-samza/repo
Commit: http://git-wip-us.apache.org/repos/asf/samza-hello-samza/commit/53ba8e3e
Tree: http://git-wip-us.apache.org/repos/asf/samza-hello-samza/tree/53ba8e3e
Diff: http://git-wip-us.apache.org/repos/asf/samza-hello-samza/diff/53ba8e3e
Branch: refs/heads/master
Commit: 53ba8e3ecbc5cb014300882f87372aaa2e7a6131
Parents: 0e14687
Author: Shenoda Guirguis <sg...@linkedin.com>
Authored: Mon Dec 24 20:59:09 2018 -0800
Committer: Aditya Toomula <at...@linkedin.com>
Committed: Mon Dec 24 20:59:09 2018 -0800
----------------------------------------------------------------------
.../sql/samza-sql-casewhen/src/main/sql/samza.sql | 12 ++++++++++++
.../sql/samza-sql-filter/src/main/sql/samza.sql | 9 +++++++++
.../java/samza/sql/PageViewGroupByOutput.json | 18 ++++++++++++++++++
.../sql/samza-sql-groupby/src/main/sql/samza.sql | 12 ++++++++++++
.../src/main/resources/log4j.xml | 16 ++++++++++++++++
.../src/main/sql/samza.sql | 11 +++++++++++
6 files changed, 78 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/samza-hello-samza/blob/53ba8e3e/src/main/java/samza/examples/sql/samza-sql-casewhen/src/main/sql/samza.sql
----------------------------------------------------------------------
diff --git a/src/main/java/samza/examples/sql/samza-sql-casewhen/src/main/sql/samza.sql b/src/main/java/samza/examples/sql/samza-sql-casewhen/src/main/sql/samza.sql
new file mode 100644
index 0000000..ca9f854
--- /dev/null
+++ b/src/main/java/samza/examples/sql/samza-sql-casewhen/src/main/sql/samza.sql
@@ -0,0 +1,12 @@
+-- For each profile in Kafka Profile change capture stream, identify whether the
+-- profile is a quality profile or not and insert the result into QualityProfile
+-- kafka topic. Please note the usage of GetSqlField UDF to extract the company
+-- name field from nested record.
+
+INSERT INTO kafka.QualityProfile
+SELECT id, status, case when (profilePicture <> null and industryName <> null and
+GetSqlField(positions, 'Position.companyName') <> null)
+then 1 else 0 end as quality
+FROM kafka.ProfileChanges
+
+-- you can add additional SQL statements here
http://git-wip-us.apache.org/repos/asf/samza-hello-samza/blob/53ba8e3e/src/main/java/samza/examples/sql/samza-sql-filter/src/main/sql/samza.sql
----------------------------------------------------------------------
diff --git a/src/main/java/samza/examples/sql/samza-sql-filter/src/main/sql/samza.sql b/src/main/java/samza/examples/sql/samza-sql-filter/src/main/sql/samza.sql
new file mode 100644
index 0000000..9e7960c
--- /dev/null
+++ b/src/main/java/samza/examples/sql/samza-sql-filter/src/main/sql/samza.sql
@@ -0,0 +1,9 @@
+-- Filter Profile change-capture stream by 'Product Manager'
+-- title and project basic profile data to a kafka topic.
+
+INSERT INTO kafka.ProductManagerProfiles
+SELECT memberId, firstName, lastName, company
+FROM kafka.ProfileChanges
+WHERE standardize(title) = 'Product Manager'
+
+-- you can add additional SQL statements here
http://git-wip-us.apache.org/repos/asf/samza-hello-samza/blob/53ba8e3e/src/main/java/samza/examples/sql/samza-sql-groupby/src/main/java/samza/sql/PageViewGroupByOutput.json
----------------------------------------------------------------------
diff --git a/src/main/java/samza/examples/sql/samza-sql-groupby/src/main/java/samza/sql/PageViewGroupByOutput.json b/src/main/java/samza/examples/sql/samza-sql-groupby/src/main/java/samza/sql/PageViewGroupByOutput.json
new file mode 100644
index 0000000..11680d1
--- /dev/null
+++ b/src/main/java/samza/examples/sql/samza-sql-groupby/src/main/java/samza/sql/PageViewGroupByOutput.json
@@ -0,0 +1,18 @@
+{
+ "name": "PageViewGroupByOutput",
+ "version" : 1,
+ "namespace": "org.apache.samza.sql.system.avro",
+ "type": "record",
+ "fields": [
+ {
+ "name":"pageKey",
+ "doc":"The page key of the page being viewed.",
+ "type":["string","null"]
+ },
+ {
+ "name": "Views",
+ "doc" : "Number of views in 5 minute window.",
+ "type": ["long", "null"]
+ }
+ ]
+}
http://git-wip-us.apache.org/repos/asf/samza-hello-samza/blob/53ba8e3e/src/main/java/samza/examples/sql/samza-sql-groupby/src/main/sql/samza.sql
----------------------------------------------------------------------
diff --git a/src/main/java/samza/examples/sql/samza-sql-groupby/src/main/sql/samza.sql b/src/main/java/samza/examples/sql/samza-sql-groupby/src/main/sql/samza.sql
new file mode 100644
index 0000000..17e4c4d
--- /dev/null
+++ b/src/main/java/samza/examples/sql/samza-sql-groupby/src/main/sql/samza.sql
@@ -0,0 +1,12 @@
+-- NOTE: Groupby Operator is not currently not fully stable,
+-- we are actively working on stabilizing it.
+
+-- Emit Page view counts collected grouped by page key in the last
+-- 5 minutes at 5 minute interval and send the result to a kafka topic.
+-- Using GetSqlField UDF to extract page key from the requestHeader.
+insert into kafka.groupbyTopic
+ select GetSqlField(pv.requestHeader) as __key__, GetPageKey(pv.requestHeader) as pageKey, count(*) as Views
+ from kafka.`PageViewEvent` as pv
+ group by GetSqlField(pv.requestHeader)
+
+-- You can add additional SQL statements here
http://git-wip-us.apache.org/repos/asf/samza-hello-samza/blob/53ba8e3e/src/main/java/samza/examples/sql/samza-sql-stream-table-join/src/main/resources/log4j.xml
----------------------------------------------------------------------
diff --git a/src/main/java/samza/examples/sql/samza-sql-stream-table-join/src/main/resources/log4j.xml b/src/main/java/samza/examples/sql/samza-sql-stream-table-join/src/main/resources/log4j.xml
new file mode 100644
index 0000000..8a80ddf
--- /dev/null
+++ b/src/main/java/samza/examples/sql/samza-sql-stream-table-join/src/main/resources/log4j.xml
@@ -0,0 +1,16 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
+<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
+ <appender name="RollingAppender" class="org.apache.log4j.RollingFileAppender">
+ <param name="File" value="${samza.log.dir}/${samza.container.name}.log" />
+ <param name="MaxFileSize" value="25MB" />
+ <param name="MaxBackupIndex" value="100" />
+ <layout class="org.apache.log4j.PatternLayout">
+ <param name="ConversionPattern" value="%d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %c{1} [%p] %m%n" />
+ </layout>
+ </appender>
+ <root>
+ <priority value="info" />
+ <appender-ref ref="RollingAppender"/>
+ </root>
+</log4j:configuration>
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/samza-hello-samza/blob/53ba8e3e/src/main/java/samza/examples/sql/samza-sql-stream-table-join/src/main/sql/samza.sql
----------------------------------------------------------------------
diff --git a/src/main/java/samza/examples/sql/samza-sql-stream-table-join/src/main/sql/samza.sql b/src/main/java/samza/examples/sql/samza-sql-stream-table-join/src/main/sql/samza.sql
new file mode 100644
index 0000000..a856440
--- /dev/null
+++ b/src/main/java/samza/examples/sql/samza-sql-stream-table-join/src/main/sql/samza.sql
@@ -0,0 +1,11 @@
+-- NOTE: Join Operator is not currently not fully stable,
+-- we are actively working on stabilizing it.
+
+-- Enrich PageViewEvent with member profile data
+INSERT INTO kafka.tracking.EnrichedPageVIewEvent
+SELECT *
+FROM Kafka.PageViewEvent as pv
+ JOIN Kafka.ProfileChanges.`$table` as p
+ ON pv.memberid = p.memberid
+
+-- You can add additional SQL statements here