Samples

The following section contains several examples that cover most of the ViyaDB features.

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

Running the query:

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

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