google-datacatalog-sisense-connector 0.1.0

Package for ingesting Sisense metadata into Google Cloud Data Catalog

google-datacatalog-sisense-connector

Package for ingesting Sisense metadata into Google Cloud Data Catalog, currently supporting the below assets:

• Folder
• Dashboard
• Widget

This sample connector creates Data Catalog tags to enable a data lineage mechanism that allows users to search the catalog to find where/which ElastiCube Table fields are used in Widgets or Dashboards. To do so, it currently processes JAQL query metadata from:

• Dashboard filters
• Widgets fields and filters
• Nested formulas
• Nested filter.by properties, used by top/bottom filters

Disclaimer: This is not an officially supported Google product.

This connector is a work in progress!

Table of Contents

• 1. Installation
  • 1.1. Mac/Linux
  • 1.2. Windows
  • 1.3. Install from source
    • 1.3.1. Get the code
    • 1.3.2. Create and activate a virtualenv
    • 1.3.3. Install the library
• 2. Environment setup
  • 2.1. Auth credentials
    • 2.1.1. Create a GCP Service Account and grant it below roles
    • 2.1.2. Download a JSON key and save it as
  • 2.2. Set environment variables
• 3. Running the connector
  • 3.1. sync-catalog
    • 3.1.1. Python entry point
    • 3.1.2. Docker entry point
  • 3.2. find-elasticube-deps
    • 3.2.1. Python entry point
    • 3.2.2. Docker entry point
  • 3.3. list-elasticube-deps
    • 3.3.1. Python entry point
    • 3.3.2. Docker entry point
• 4. Developer environment
  • 4.1. Install and run the YAPF formatter
  • 4.2. Install and run the Flake8 linter
  • 4.3. Run Tests
  • 4.4. Additional resources
• 5. Templates, Tags, and Data Lineage
• 6. Troubleshooting
  • 6.1. Sisense APIs compatibility
  • 6.2. Data Catalog quota

---

1. Installation

Install this library in a virtualenv using pip. Virtualenv is a tool to create isolated Python environments. The basic problem it addresses is one of dependencies and versions, and indirectly permissions.

With virtualenv, it's possible to install this library without needing system install permissions, and without clashing with the installed system dependencies. Make sure you use Python 3.6+.

1.1. Mac/Linux

pip3 install virtualenv
virtualenv --python python3.6 <your-env>
source <your-env>/bin/activate
<your-env>/bin/pip install google-datacatalog-sisense-connector

1.2. Windows

pip3 install virtualenv
virtualenv --python python3.6 <your-env>
<your-env>\Scripts\activate
<your-env>\Scripts\pip.exe install google-datacatalog-sisense-connector

1.3. Install from source

1.3.1. Get the code

git clone https://github.com/GoogleCloudPlatform/datacatalog-connectors-bi/
cd datacatalog-connectors-bi/google-datacatalog-sisense-connector

1.3.2. Create and activate a virtualenv

pip3 install virtualenv
virtualenv --python python3.6 <your-env>
source <your-env>/bin/activate

1.3.3. Install the library

pip install .

2. Environment setup

2.1. Auth credentials

2.1.1. Create a GCP Service Account and grant it below roles

• Data Catalog Admin

2.1.2. Download a JSON key and save it as

• <YOUR-CREDENTIALS_FILES_FOLDER>/sisense2dc-datacatalog-credentials.json

Please notice this folder and file will be required in next steps.

2.2. Set environment variables

Replace below values according to your environment:

export GOOGLE_APPLICATION_CREDENTIALS=datacatalog_credentials_file

export SISENSE2DC_SISENSE_SERVER=sisense_server
export SISENSE2DC_SISENSE_USERNAME=sisense_username
export SISENSE2DC_SISENSE_PASSWORD=sisense_password
export SISENSE2DC_DATACATALOG_PROJECT_ID=google_cloud_project_id
export SISENSE2DC_DATACATALOG_LOCATION_ID=google_cloud_location_id

Replace above values according to your environment. The Data Catalog credentials file was saved in step 2.1.2.

3. Running the connector

3.1. sync-catalog

Synchronizes Google Data Catalog with a given Sisense server.

• The --datacatalog-location-id argument is optional and defaults to us.

3.1.1. Python entry point

• Virtualenv

google-datacatalog-sisense-connector sync-catalog \
  --sisense-server $SISENSE2DC_SISENSE_SERVER \
  --sisense-username $SISENSE2DC_SISENSE_USERNAME \
  --sisense-password $SISENSE2DC_SISENSE_PASSWORD \
  --datacatalog-project-id $SISENSE2DC_DATACATALOG_PROJECT_ID \
  [--datacatalog-location-id $SISENSE2DC_DATACATALOG_LOCATION_ID]

3.1.2. Docker entry point

docker build --rm --tag sisense2datacatalog .
docker run --rm --tty -v YOUR-CREDENTIALS_FILES_FOLDER:/data \
  sisense2datacatalog sync-catalog \
  --sisense-server $SISENSE2DC_SISENSE_SERVER \
  --sisense-username $SISENSE2DC_SISENSE_USERNAME \
  --sisense-password $SISENSE2DC_SISENSE_PASSWORD \
  --datacatalog-project-id $SISENSE2DC_DATACATALOG_PROJECT_ID \
  [--datacatalog-location-id $SISENSE2DC_DATACATALOG_LOCATION_ID]

3.2. find-elasticube-deps

Finds ElastiCube dependencies through catalog search and prints them in the console.

3.2.1. Python entry point

• Virtualenv

google-datacatalog-sisense-connector find-elasticube-deps \
  --datasource <datasource> \
  --table <table> \
  --column <column> \
  --datacatalog-project-id $SISENSE2DC_DATACATALOG_PROJECT_ID

3.2.2. Docker entry point

docker build --rm --tag sisense2datacatalog .
docker run --rm --tty -v YOUR-CREDENTIALS_FILES_FOLDER:/data \
  sisense2datacatalog find-elasticube-deps \
  --datasource <datasource> \
  --table <table> \
  --column <column> \
  --datacatalog-project-id $SISENSE2DC_DATACATALOG_PROJECT_ID

3.3. list-elasticube-deps

Lists ElastiCube dependencies for a given Sisense Dashboard or Widget and prints them in the console.

3.3.1. Python entry point

• Virtualenv

google-datacatalog-sisense-connector list-elasticube-deps \
  --asset-url <asset-url> \
  --datacatalog-project-id $SISENSE2DC_DATACATALOG_PROJECT_ID

3.3.2. Docker entry point

docker build --rm --tag sisense2datacatalog .
docker run --rm --tty -v YOUR-CREDENTIALS_FILES_FOLDER:/data \
  sisense2datacatalog list-elasticube-deps \
  --asset-url <asset-url> \
  --datacatalog-project-id $SISENSE2DC_DATACATALOG_PROJECT_ID

4. Developer environment

4.1. Install and run the YAPF formatter

pip install --upgrade yapf

# Auto update files
yapf --in-place --recursive src tests

# Show diff
yapf --diff --recursive src tests

# Set up pre-commit hook
# From the root of your git project.
curl -o pre-commit.sh https://raw.githubusercontent.com/google/yapf/master/plugins/pre-commit.sh
chmod a+x pre-commit.sh
mv pre-commit.sh .git/hooks/pre-commit

4.2. Install and run the Flake8 linter

pip install --upgrade flake8
flake8 src tests

4.3. Run Tests

python setup.py test

4.4. Additional resources

Please refer to the Developer Resources documentation.

5. Templates, Tags, and Data Lineage

The Data Catalog Tag Templates created by this connector and their usage scenarios are described below:

TAG TEMPLATE	FIELDS	USAGE
Folder Metadata (sisense_folder_metadata)	Id Owner username Owner name Id of Parent Parent Folder Data Catalog Entry for the parent Folder Has children Child count Has dashboards Dashboard count Sisense Server Url	Store additional metadata for Folder-related Entries.
Dashboard Metadata (sisense_dashboard_metadata)	Id Owner username Owner name Folder Id Folder Name Data Catalog Entry for the Folder Data Source Time it was last published Time it was last opened Sisense Server Url	Store additional metadata for Dashboard-related Entries.
Widget Metadata (sisense_widget_metadata)	Id Type Subtype Owner username Owner name Dashboard Id Dashboard Title Data Catalog Entry for the Dashboard Data Source Sisense Server Url	Store additional metadata for Widget-related Entries.
JAQL Metadata (sisense_jaql_metadata)	Table Column Dimension Formula Aggregation Sisense Server Url	Store JAQL metadata for ElasticCube-dependent entities such as Dashboard filters, Widget fields and filters.

Please notice the connector creates Data Catalog Tags for most of the Dashboard and Widget properties that depend on JAQL queries, e.g., fields, filters, nested formulas, and top/bottom filters. Such tags, created from the JAQL Metadata template, are quite simple: ~4 fields each. The connector uses lots of them to enable column-level lineage tracking for a given Sisense server.

6. Troubleshooting

6.1. Sisense APIs compatibility

The connector may fail during the scrape stage if the Sisense API do not return metadata in the expected format. As a reference, the below versions were already validated:

• Sisense REST API v1.0

VERSION	RESULT
Windows 8.2.5	SUCCESS

6.2. Data Catalog quota

In case a connector execution hits Data Catalog quota limit, an error will be raised and logged with the following details, depending on the performed operation (READ/WRITE/SEARCH):

status = StatusCode.RESOURCE_EXHAUSTED
details = "Quota exceeded for quota metric 'Read requests' and limit 'Read requests per minute' of service 'datacatalog.googleapis.com' for consumer 'project_number:1111111111111'."
debug_error_string = 
"{"created":"@1587396969.506556000", "description":"Error received from peer ipv4:172.217.29.42:443","file":"src/core/lib/surface/call.cc","file_line":1056,"grpc_message":"Quota exceeded for quota metric 'Read requests' and limit 'Read requests per minute' of service 'datacatalog.googleapis.com' for consumer 'project_number:1111111111111'.","grpc_status":8}"

For more information on Data Catalog quota, please refer to the product documentation.