layout: page title: How to use Samza SQL

There are couple of ways to use Samza SQL

  1. Run Samza SQL on your local machine.
  2. Run Samza SQL on YARN.

Running Samza SQL on your local machine

Samza SQL console tool documented here uses Samza standalone to run the Samza SQL on your local machine. This is the quickest way to play with Samza SQL. Please follow the instructions here to get access to the Samza tools on your machine.

Start the Kafka server

Please follow the instructions from the Kafka quickstart to start the zookeeper and Kafka server.

Create ProfileChangeStream Kafka topic

The below sql statements requires a topic named ProfileChangeStream to be created on the Kafka broker. You can follow the instructions in the Kafka quick start guide to create a topic named “ProfileChangeStream”.

{% highlight bash %} ./bin/kafka-topics.sh --create --zookeeper localhost:2181 --replication-factor 1 --partitions 1 --topic ProfileChangeStream {% endhighlight %}

Generate events into ProfileChangeStream topic

Use generate-kafka-events from Samza tools to generate events into the ProfileChangeStream

{% highlight bash %} cd samza-tools- ./scripts/generate-kafka-events.sh -t ProfileChangeStream -e ProfileChange {% endhighlight %}

Using Samza SQL Console to run Samza sql on your local machine

Below are some of the sql queries that you can execute using the samza-sql-console tool from Samza tools package.

{% highlight bash %}

This command just prints out all the events in the Kafka topic ProfileChangeStream into console output as a json serialized payload.

./scripts/samza-sql-console.sh --sql “insert into log.consoleoutput select * from kafka.ProfileChangeStream”

This command prints out the fields that are selected into the console output as a json serialized payload.

./scripts/samza-sql-console.sh --sql “insert into log.consoleoutput select Name, OldCompany, NewCompany from kafka.ProfileChangeStream”

This command showcases the RegexMatch udf and filtering capabilities.

./scripts/samza-sql-console.sh --sql “insert into log.consoleoutput select Name as key, Name, NewCompany, RegexMatch(‘.*soft’, OldCompany) from kafka.ProfileChangeStream where NewCompany = ‘LinkedIn’” {% endhighlight %}

Running Samza SQL on YARN

The hello-samza project is an example project designed to help you run your first Samza application. It has examples of applications using the low level task API, high level API as well as Samza SQL.

This tutorial demonstrates a simple Samza application that uses SQL to perform stream processing.

Get the hello-samza Code and Start the grid

Please follow the instructions from hello-samza-high-level-yarn on how to build the hello-samza repository and start the yarn grid.

Create the topic and generate Kafka events

Please follow the steps in the section “Create ProfileChangeStream Kafka topic” and “Generate events into ProfileChangeStream topic” above.

Build a Samza Application Package

Before you can run a Samza application, you need to build a package for it. Please follow the instructions from hello-samza-high-level-yarn on how to build the hello-samza application package.

Run a Samza Application

After you've built your Samza package, you can start the app on the grid using the run-app.sh script.

{% highlight bash %} ./deploy/samza/bin/run-app.sh --config-factory=org.apache.samza.config.factories.PropertiesConfigFactory --config-path=file://$PWD/deploy/samza/config/page-view-filter-sql.properties {% endhighlight %}

The app executes the following SQL command : {% highlight sql %} insert into kafka.NewLinkedInEmployees select Name from ProfileChangeStream where NewCompany = ‘LinkedIn’ {% endhighlight %}

This SQL performs the following

  1. Consumes the Kafka topic ProfileChangeStreamStream which contains the avro serialized ProfileChangeEvent(s)
  2. Deserializes the events and filters out only the profile change events where NewCompany = ‘LinkedIn’ i.e. Members who have moved to LinkedIn.
  3. Writes the Avro serialized event that contains the Id and Name of those profiles to Kafka topic NewLinkedInEmployees.

Give the job a minute to startup, and then tail the Kafka topic:

{% highlight bash %} ./deploy/kafka/bin/kafka-console-consumer.sh --zookeeper localhost:2181 --topic NewLinkedInEmployees {% endhighlight %}

Congratulations! You've now setup a local grid that includes YARN, Kafka, and ZooKeeper, and run a Samza SQL application on it.

Shutdown and cleanup

To shutdown the app, use the same run-app.sh script with an extra --operation=kill argument {% highlight bash %} ./deploy/samza/bin/run-app.sh --config-factory=org.apache.samza.config.factories.PropertiesConfigFactory --config-path=file://$PWD/deploy/samza/config/page-view-filter-sql.properties --operation=kill {% endhighlight %}

Please follow the instructions from Hello Samza High Level API - YARN Deployment on how to shutdown and cleanup the app.