Samples

The following section contains several examples that cover most of the ViyaDB features using single database instance.

Running

It's recommended to use Docker image of ViyaDB for running the samples. To launch Docker container with latest ViyaDB version, run:

docker run -p 5000:5000 --rm -ti \
  -v /tmp/viyadb:/tmp/viyadb viyadb/viyadb:latest

Mobile Attribution Tracking

Mobile attribution tracking is a powerful marketing tool that allows measuring productivity of users, advertisement campaigns, etc. The biggest question that tools like this can answer is where has advertiser's money gone, and how to optimize future campaigns.

How does this work? Mobile application generates events upon user action, and delivers them to a server, where these events are being joined to the attribution data received from an ad network and different partners. Finally, the data is analyzed based on a date an application was installed. What we need to know for the sake of example is that we have events of the following types:

• Click (clicking on an advertisement)
• Impression (viewing an advertisement)
• Install (installing an application)
• In-App Event (custom in-app event generated by an application)
• Launch (application was launched)
• Uninstall (application was removed from a mobile phone)

In case of click and impression events, install date actually means click/impression date.

Starting ViyaDB

Create table.json file containing:

{
  "name": "activity",
  "dimensions": [
    {"name": "app_id"},
    {"name": "user_id"},
    {
      "name": "event_time", "type": "time",
      "format": "millis", "granularity": "day"
    },
    {"name": "country"},
    {"name": "city"},
    {"name": "device_type"},
    {"name": "device_vendor"},
    {"name": "ad_network"},
    {"name": "campaign"},
    {"name": "site_id"},
    {"name": "event_type"},
    {"name": "event_name"},
    {"name": "organic", "cardinality": 2},
    {"name": "days_from_install", "type": "ushort"}
  ],
  "metrics": [
    {"name": "revenue" , "type": "double_sum"},
    {"name": "users", "type": "bitset", "field": "user_id"},
    {"name": "count" , "type": "count"}
  ]
}

Create the table by running:

curl -d @table.json http://localhost:5000/tables 

Generating Sample Data

To generate 100M user activity events, run the following:

mkdir /tmp/viyadb
docker run --log-driver=none --rm -ti -e EVENTS_NUMBER=10000000 \
  -e OUTPUT_FORMAT=tsv viyadb/events-generator:latest > /tmp/viyadb/data.tsv

This might take several minutes.

Loading Data

Create load descriptor as follows:

{
  "table": "activity",
  "format": "tsv",
  "type": "file",
  "columns": [
    "app_id", "user_id", "event_time", "country", "city",
    "device_type", "device_vendor", "ad_network", "campaign",
    "site_id", "event_type", "event_name", "organic",
    "days_from_install", "revenue"
  ],
  "file": "/tmp/viyadb/data.tsv"
}

Load the data by running:

curl -d @load.json http://localhost:5000/load

Querying

The following query finds top 10 applications by organic installs count:

{
  "type": "aggregate",
  "table": "activity",
  "select": [
    {"column": "app_id"},
    {"column": "count"}
  ],
  "filter": {"op": "and", "filters": [
    {"op": "ge", "column": "event_time", "value": "2015-01-01"},
    {"op": "le", "column": "event_time", "value": "2015-01-30"},
    {"op": "eq", "column": "event_type", "value": "install"},
    {"op": "eq", "column": "organic", "value": "True"}
  ]},
  "sort": [{"column": "count"}],
  "limit": 10
}

Save the query in file query.json, and run:

curl -d @query.json http://localhost:5000/query

The query can be invoked using experimental SQL support. To enter SQL interpreter shell, please run:

docker exec -ti \
  $(docker ps | grep viyadb/viyadb:latest | awk '{print $1}') \
  /opt/viyadb/bin/vsql

Our previous query translates into SQL as follows:

SELECT
  app_id, count
FROM
  activity
WHERE
  event_time >= '2015-01-01' AND
  event_time <= '2015-01-30' AND
  event_type='install' AND
  organic='True'
ORDER BY
  count DESC
LIMIT 10

Let's run the query:

ViyaDB> SELECT app_id, count FROM activity WHERE event_time >= '2015-01-01' AND event_time <= '2015-01-30' AND event_type='install' AND organic='True' ORDER BY count DESC LIMIT 10

app_id                                 count
------                                 -----
com.skype.raider                       282
com.facebook.orca                      42
net.cleverbit.VegeFruits               41
com.virgil.basketball                  27
com.abzorbagames.poker                 21
air.nn.mobile.app.main                 18
com.squareup                           17
freemoviesapp.com                      16
com.ea.spymouse_row                    14
com.bandainamcoent.tamagothiclassicus  14

Time taken: 0.004841 secs

For more example on this dataset please refer to the Blog post: Analyzing Mobile Users Activity with ViyaDB.