# Big Table Reader
Big Table Reader command line tool

## Build

In order to build the tool use command:

```
sbt clean assembly
```

This command will create a jar file in the target/scala-2.11 folder. It can be run using following command (find amazon_streams by row_key in dev env):

```
java -jar delphi-bt-reader-<VERSION>.jar -f amazon_streams artist_date_day~ARFSB0700000~2020-02-26~amazonprime
```

## Usage

### Databricks Notebook

To use reader with Databricks notebook, the `delphi-bt-reader` library must be installed 
to the cluster attached to the notebook. The following list of clusters has the library installed by default:
1. `delphi-etl-manual-testing-cluster`
2. `stage-delphi-etl-manual-testing-cluster`
3. `qa-delphi-etl-manual-testing-cluster`
4. `dev-delphi-etl-manual-testing-cluster`   

To be able to execute queries, there is a need to create a paragraph for registering views.
The following piece of code registers temp view `spotify` with a default settings:  
```scala
import com.sonymusic.delphi.btreader._

BigTableReader(spark).readTable("spotify")
```  
These two lines of code are enough for executing SQL queries in a
next paragraph:  
```sql
SELECT row_id, report_date, isrc, streams
FROM spotify 
LIMIT 10;
```  
Please make sure paragraphs in a notebook have a corrent language settings. When notebook's default 
language is SQL, paragraphs with scala code must be annotated with `%scala` (1):
```scala
%scala (1)

import com.sonymusic.delphi.btreader._

BigTableReader(spark).readTable("spotify")
``` 

When notebook's default language is scala, then paragraphs with SQL code must be annotated with `%sql` (2):
```sql
%sql (2)

SELECT row_id, report_date, isrc, streams
FROM spotify 
LIMIT 10;
```  

More details about notebook usage could be found in the Databricks doc: https://docs.databricks.com/notebooks/notebooks-use.html

#### Catalogs
As far as BigTable is a NoSQL DB, but we would like to read data from it via SQL queries and get
results in a flat table, we have to use some kind of mapping between KV column families and output
table structure. Such mapping is called a "catalog" (https://github.com/hortonworks-spark/shc/blob/master/README.md#catalog).
The catalog is a JSON string which has to be provided to
Spark connector to transform BigTable tables to DataFrames. The catalog is responsible for configuring the list
of columns in the output table and mapping those columns to columns in the BigTable table.  
Delphi Big Table Reader has a number of preconfigured catalogs:
- `amazon_music`
- `amazon_music-validation`
- `apple_music`
- `apple_music-validation`
- `chartmetric`
- `charts`
- `playlists`
- `spotify`
- `spotify-validation`
- `tiktok`
- `tiktok-validation`
- `youtube`
- `youtube-validation`
- `playlist_tracks`
- `apple_fact_public_playlist_daily`
- `spotify_fact_public_playlist_daily`
- `spotify_fact_playlist_followers_daily`
- `brand_tagging_track_id`
- `brand_tagging_upc`
- `fact_tiktok_top_isrc_sounds_weekly`

When the catalog name is equal to BigTable's table name there is no need to configure the catalog manually.
The catalog will be selected by table name among default catalogs by using `readTable("...")` method:
```scala
import com.sonymusic.delphi.btreader._

BigTableReader(spark).readTable("charts")
```
> :warning: `readTable("...")` should be used to get default catalog.

Otherwise, when catalog name is differs from the table name, we need to configure the catalog manually
during the registration of a temp view:
```scala
import com.sonymusic.delphi.btreader._

BigTableReader(spark)
  .catalogName("spotify-validation")
  .read()
```
> :warning: `read()` operation should be used instead of `readTable("table")` when catalog name is provided manually.

As was mentioned earlier, natively, catalog is a simple JSON-based mapping between input and output tables.
The reader supports usage of a custom catalogs prepared as a JSON strings:
```scala
import com.sonymusic.delphi.btreader._

val catalogJson =
  """{
    |  "rowkey": "row_id",
    |  "columns": {
    |    "row_id": {
    |      "cf": "rowkey",
    |      "col": "row_id",
    |      "type": "string"
    |    },
    |    "streams": {
    |      "cf": "validation",
    |      "col": "streams",
    |      "type": "long"
    |    }
    |  },
    |  "table": {
    |    "namespace": "default",
    |    "name": "apple_music"
    |  }
    |}
    |""".stripMargin

BigTableReader(spark)
  .catalog(catalogJson)
  .read()
```
> :warning: `read()` operation should be used instead of `readTable("table")` when custom catalog is required. 

#### Custom View Name
By default, the registered view will have the same name as a target BigTable table.
But from time to time there is a need to use a custom view name. 
For example when there is a need to read the same table into a different view using a different catalog. 
To do this the following example might be helpful:
```scala
import com.sonymusic.delphi.btreader._

BigTableReader(spark)
  .catalogName("spotify-validation")
  .viewName("spotify_validation")
  .readTable("spotify")
```
Then, the data from `spotify` table can be used in the following way:
```sql
SELECT *
FROM spotify_validation;
```

#### Field Decoding
The Delphi aggregation data stored in the BigTable may have binary columns with protobuf serialized values.
BT reader provides a built-in functionality to deal with Delphi protobuf messages and provide internal values in 
a human-friendly format. Automatic deserialization for protobuf columns are implemented for the following tables:
- `amazon_music`
- `apple_music`
- `spotify`
- `tiktok`
- `youtube`
- `playlists`
- `playlist_tracks`
- `top_sounds`

To be able to provide query results in the human-friendly format for other tables a couple of 
decoders were implemented:
- `ToObjectProtoDecoder`

The following example will deserialize protobuf binary columns. 
In this example `streams` and `demographics` columns will be decoded from protobuf binary and added to the DataFrame 
as a nested structure:

```scala
import com.sonymusic.delphi.btreader._
import com.sonymusic.delphi.btreader.decoder.proto._
import com.sonymusic.delphi.etl.apps.proto.apple._

BigTableReader(spark)
  .binaryDecoder("streams", DataFrameProtoColumnDecoder(ProtoDecoder(AppleCountryStats)))
  .binaryDecoder("demographics", DataFrameProtoColumnDecoder(ProtoDecoder(AppleCountryStats)))
  .readTable("apple_music")
```

There is a possibility to prevent protobuf naming in the output result and make decoded object have fields 
in snake case naming. To do that, there is a need to add additional flag to the decoder:
```scala
BigTableReader(spark)
  .binaryDecoder("streams", DataFrameProtoColumnDecoder(ProtoDecoder(AppleCountryStats), protoNaming = true))
  .binaryDecoder("demographics", DataFrameProtoColumnDecoder(ProtoDecoder(AppleCountryStats), protoNaming = true))
  .readTable("apple_music")
```
> :warning: Proto naming **is enabled by default** when no decoders are configured.

### Command line

:warning: this functionality is deprecated. The support of it was already stopped and it will be removed completely.

Available commands:  

find: `-f table_name row_key` (example: `-f amazon_streams artist_date_day~ARFSB0700881~2020-02-26~amazonprime`)  
random [for report date]: `[-rd yyyy-mm-dd] -r table_name limit` (example: `-rd 2020-03-20 -r amazon_streams 10`)  
prefix [for report date]: `[-rd yyyy-mm-dd] -p table_name prefix limit` (example `-rd 2020-03-20 -p amazon_streams artist_date_day 10`)  
list tables: `-lt`  

Also, you can prefix commands find, random, prefix with `-countries us,gb,fr` which will keep only mentioned countries in the response.

#### Authentication
In order to be able to run reader as a command line application, there is a need to get temporary AWS credentials using
the following command:
```bash
% aws sts assume-role --output json --profile gdb-infra-dev --role-arn arn:aws:iam::475275892927:role/cross_account_qa --duration-seconds 43200 --role-session-name assumed-session --serial-number <YOUR ROLE ARN> --token-code <MFA CODE> 
```
Requested credentials have limited lifetime and should be updated once expired using the same command.

Output of this command should look like the following:
```json
{
    "Credentials": {
        "AccessKeyId": "ASIAW5KFGTC74CRDAQNJ",
        "SecretAccessKey": "dgeoZVWkKpBahVaaTgD2SzibyZC+TD2mz1n6rcqU",
        "SessionToken": "IQoJb3JpZ2luX2VjEC0aCXVzLWVhc3QtMSJLDOEUCIHLosvpZLjN+zucyFM+V1BiAZesV3ty9pi9cPFBq0COmAiEAyzT28g23Zuer8WKnBYYP1tk0HDmDb5NXYZkbn30YAtgqnQIIFRAAGgw0NzUyNzU4OTI5MjciDF7UdRxiRFDcnM1P5ir6AY87KPMHBFY/eOqjopxIUItclVZy1sG5opa4dwLWWEvbYaE3GiJWYyPS2hT2Z3viiqKmmo8jVxwdoNqFSUWy247pr4WdKQnCqKTOOw+k+cr4CGwgYmZJ9QjxWjjVguTKMmbvsjUW7WY6kd4zsAl0vUQDeZv4nkqJXZKmZ8xlIViE+dkWDlRGXJwNTRnuHguB3iaoTdKgn6BbppM9RSFx/lJoJ7DjidnbewqW9BBwZ5qSIToMQ2SvSouWIzlEObnFf67Bkkdze2DMIDK/CM1cllOiYZaouciv7+eOAUR8MC8GNEd2LkeYv7NevfSpnduzwzF7rgO2OliZ1DEwv4jv+QU6nQHGx985GieBz9VvQgLoMq68TrZPaHfXDOyGhigCd1JAb4eQa4Yphapi8jkMn9SMC13CYKVMvQKRgwkxLQEuaGwNDC+T1cqU4dxlH+GeuG3aWQd+3pE1NouRHkIb4kmSgt/tj5q9nvXM0gYnjcuhHyDY8jVCKmC9fddLXy4a3XXBvxNl4PeMf/Phcp8K+9xGBmNax6GYZ3bhj+WeqjmZ",
        "Expiration": "2020-08-19T00:06:23+00:00"
    },
    "AssumedRoleUser": {
        "AssumedRoleId": "AROAW5KFGTC7XUOGXPBVX:assumed-session",
        "Arn": "arn:aws:sts::475275892927:assumed-role/cross_account_qa/assumed-session"
    }
}
```

There are two options to apply requested temporary credentials: environment variables and java system properties.

To use credentials via environment variables, the following variables must be configured:
```
AWS_ACCESS_KEY_ID=ASIAW5KFGTC74CRDAQNJ
AWS_SECRET_ACCESS_KEY=dgeoZVWkKpBahVaaTgD2SzibyZC+TD2mz1n6rcqU
AWS_SESSION_TOCKEN=IQoJb3JpZ2luX2VjEC0aCXVzLWVhc3QtMSJLDOEUCIHLosvpZLjN+zucyFM+V1BiAZesV3ty9pi9cPFBq0COmAiEAyzT28g23Zuer8WKnBYYP1tk0HDmDb5NXYZkbn30YAtgqnQIIFRAAGgw0NzUyNzU4OTI5MjciDF7UdRxiRFDcnM1P5ir6AY87KPMHBFY/eOqjopxIUItclVZy1sG5opa4dwLWWEvbYaE3GiJWYyPS2hT2Z3viiqKmmo8jVxwdoNqFSUWy247pr4WdKQnCqKTOOw+k+cr4CGwgYmZJ9QjxWjjVguTKMmbvsjUW7WY6kd4zsAl0vUQDeZv4nkqJXZKmZ8xlIViE+dkWDlRGXJwNTRnuHguB3iaoTdKgn6BbppM9RSFx/lJoJ7DjidnbewqW9BBwZ5qSIToMQ2SvSouWIzlEObnFf67Bkkdze2DMIDK/CM1cllOiYZaouciv7+eOAUR8MC8GNEd2LkeYv7NevfSpnduzwzF7rgO2OliZ1DEwv4jv+QU6nQHGx985GieBz9VvQgLoMq68TrZPaHfXDOyGhigCd1JAb4eQa4Yphapi8jkMn9SMC13CYKVMvQKRgwkxLQEuaGwNDC+T1cqU4dxlH+GeuG3aWQd+3pE1NouRHkIb4kmSgt/tj5q9nvXM0gYnjcuhHyDY8jVCKmC9fddLXy4a3XXBvxNl4PeMf/Phcp8K+9xGBmNax6GYZ3bhj+WeqjmZ
```

To use credentials as Java System properties, the following properties should be configured during application execution:
```
-Daws.accessKeyId=ASIAW5KFGTC74CRDAQNJ
-Daws.secretKey=dgeoZVWkKpBahVaaTgD2SzibyZC+TD2mz1n6rcqU
-Daws.sessionToken=IQoJb3JpZ2luX2VjEC0aCXVzLWVhc3QtMSJLDOEUCIHLosvpZLjN+zucyFM+V1BiAZesV3ty9pi9cPFBq0COmAiEAyzT28g23Zuer8WKnBYYP1tk0HDmDb5NXYZkbn30YAtgqnQIIFRAAGgw0NzUyNzU4OTI5MjciDF7UdRxiRFDcnM1P5ir6AY87KPMHBFY/eOqjopxIUItclVZy1sG5opa4dwLWWEvbYaE3GiJWYyPS2hT2Z3viiqKmmo8jVxwdoNqFSUWy247pr4WdKQnCqKTOOw+k+cr4CGwgYmZJ9QjxWjjVguTKMmbvsjUW7WY6kd4zsAl0vUQDeZv4nkqJXZKmZ8xlIViE+dkWDlRGXJwNTRnuHguB3iaoTdKgn6BbppM9RSFx/lJoJ7DjidnbewqW9BBwZ5qSIToMQ2SvSouWIzlEObnFf67Bkkdze2DMIDK/CM1cllOiYZaouciv7+eOAUR8MC8GNEd2LkeYv7NevfSpnduzwzF7rgO2OliZ1DEwv4jv+QU6nQHGx985GieBz9VvQgLoMq68TrZPaHfXDOyGhigCd1JAb4eQa4Yphapi8jkMn9SMC13CYKVMvQKRgwkxLQEuaGwNDC+T1cqU4dxlH+GeuG3aWQd+3pE1NouRHkIb4kmSgt/tj5q9nvXM0gYnjcuhHyDY8jVCKmC9fddLXy4a3XXBvxNl4PeMf/Phcp8K+9xGBmNax6GYZ3bhj+WeqjmZ
```

#### Environment Switch
By default, application is configured to read `PROD` environment. To switch it, the following Java
System Property must be configured during the run:
```
java -Dconfig.resource=<ENV PROPERTY FILE> -jar delphi-bt-reader.jar <OPTIONS>
```
The following property files are available out of the box:
- `application.stage.conf`
- `application.dev.conf`
- `application.qa.conf`

#### Examples:
Read from QA env:
```
java -Dconfig.resource=application.qa.conf -jar delphi-bt-reader.jar -f amazon_streams artist_date_day~ARFSB0700000~2020-02-26~amazonprime
```

Read from STAGE env with credentials:
```
java -Dconfig.resource=application.stage.conf -Daws.accessKeyId=ASIAW5KFGTC7VXUXLGWH -Daws.secretKey=dDf8Rg2UQt5rPKMJeNuHtjGwtrl2rfA7GDUugz0E -Daws.sessionToken=IQoJb3JpZ2luX2VjECsaCXVzLWVhc3QtMSJGMEQCIFm5j4uy7C6uI9z0m3SF8ldf8rAYpVkY8D0FiNFDt81kAiB5ANW4eqhGl0L7SMyM7E0GgX7qE7PBj4nOiHqlcEPxVyqdAggUEAAaDDQ3NTI3NTg5MjkyNyIM+L0ApuxKBZuGqmooKvoB6La018mutVsrQITZPmzufzmCvVh6X/el1jxD6db8VHptlmFiHaDLCiuzop/xO3Rc37XOFZv8PAH9K+XupsPo32+ZOs+O+N1cht3gneiYHC0u6BqaoIDWUVGWgrhoR44a20qqnsOob9TFLoRZhUg7B40NcqAgPCVgS4kcWzSR09fg9ti88B6LhK6oD/5uuVVKhToVCQ6gjj3RFnJ2OIx/exSopIHmKIi+gsRZyVhzqBRLkc0vSY5zfTSyUVluGcsQtVqwPB8pyLCBnpV1lrwmwnPvQ2wu22axcSjnFKT1Y/KKxnXSTkwD5zaLG4aYcyJUpB4t5egIMSGUDzDD5+75BTqeAX1OB71v0ekbI8i500bFqwbtSIJm7VAfTDO0dWFmn6wssaB7d5l8JocAP5FiOLRGRdhxi4Vi9JJCQsZVsy+SKq/BZyEqX9Pgd2eFLWCUxlMTQ7gEdzQgtDXNWnJO3NgY1VnwUcpWUkd9qj5oxx5q/g80WYl87jKCU7yw0UktIx01C82/ZE9w3lnDiEc7prD5Mfxc6d4IpmVsAF8YjlXQ -jar delphi-bt-reader.jar -f amazon_streams artist_date_day~ARFSB0700000~2020-02-26~amazonprime
```