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