composapy 0.13.1

ComposableAnalytics csharp binding for python

Composapy

DataLabs comes pre-installed with Composapy, a python package that makes it easy to programmatically interface with Composable. Composapy allows you to interactively author, execute and retrieve results from DataFlows (Directed Acyclic Graphs, DAGs).

Visit our ReadTheDocs page a more detailed look at Composapy.

• Session
  • Register a Session
• DataFlow
  • Create and Save a DataFlow
  • Run a DataFlow
  • DataFlowObject and DataFlowRun Modules
  • DataFlowObject and DataFlowRun Input
  • DataFlowRun Result
• Key
  • Retrieve Key
  • Register Key
  • Search for Key
• QueryView
  • Create and Connect to a QueryView Driver
  • Run a Query
  • SQL Magic Commands
  • Run a Saved QueryView
• Interactive Table Output
  • QueryView Driver Output
  • SQL Cell Magic Output
  • Saved QueryView Output
  • Table Contract Output
• Additional Information

Session

ReadTheDocs - Session

Register a Session

Composapy looks for the environment variable APPLICATION_URI by default (set by DataLabs). If you are using Composapy outside of the datalabs environment and the APPLICATION_URI environment variable is not set, you can set it with keyword argument uri. You can create a session with Windows Authentication (if you are in DataLab, this will be the same as the key on the DataLab edit screen), [string] API Token (can be generated on the composable website), or with a [tuple[str, str]] username/password combination.

from composapy.session import Session
from composapy.auth import AuthMode

session = Session(auth_mode=AuthMode.WINDOWS)
session = Session(auth_mode=AuthMode.TOKEN, credentials="<your-api-token-here>", uri="http://localhost/CompAnalytics/")
session = Session(auth_mode=AuthMode.FORM, credentials=("username", "password"))

After creating a session, register it (future operations use this session by default). Optionally, enable the save flag to write to a local config file for automatical loading/registration on next Composapy import.

session.register()
session.register(save=True)

You can also call get_session to get the currently registered session.

from composapy.session import get_session

session = get_session()

DataFlow

ReadTheDocs - DataFlow

Create and Save a DataFlow

DataFlowObject's can be initialized with the DataFlow API method - create. It takes either a json string (kwarg json) or path to a json file (kwarg file_path) as parameters. Call the save method on an unsaved DataFlowObject to save it in the Composable database. Saving it will make give it an id, making it available for use in the Composable Designer.

from composapy.dataflow.api import DataFlow

dataflow_object = DataFlow.create(file_path="simple-dataflow.json")         # DataFlowObject(id=None)
dataflow_object = DataFlow.create(file_path="simple-dataflow.json").save()  # DataFlowObject(id=123456)

Run a DataFlow

To run a saved DataFlow, you can retrieve the appId in the DataFlow's URL.

dataflow_run = DataFlow.run(444333)  # DataFlowRun(id=444333)

To run a DataFlow that has external input modules, use the external_inputs kwarg, which is a dictionary with key equal to the external modules name field and the value equal to what you want to pass in.

dataflow_run = DataFlow.run(444333, external_inputs={"a_string_external_input": "foo string"})  # DataFlowRun(id=444333)

DataFlowObject and DataFlowRun Modules

dataflow_run.modules[0]  # Module(name='Calculator', type=Calculator)

DataFlowObject and DataFlowRun Input

dataflow_object.modules.filter(name="calc module name")[0].inputs.first()  # Input(name=Param1, type=Double, value=1.0)

DataFlowRun Result

dataflow_run.modules.get(name="string module name").result  # Result(name='foo name', type=String, value='foo value')

Key

ReadTheDocs - Key

Retrieve Key

A Composapy Key can be retrieved using the python Key API.

from composapy.key.api import Key

key_object = Key.get(123456)  # KeyObject(name='some name', type='StringConnectionSettings')

Optionally, if your key name is unique, you can retrieve it by name.

key_object = Key.get(name="a unique name")  # KeyObject(name='a unique name', type='SqlConnectionSettings')

Register Key

Similar to Session, KeyObjects can be registered with the save=True flag. This will save the key id in the working directory's "composapy.ini" file, which will then be loaded when the Composapy package is imported.

key_object.register(save=True)

You can retrieve the currently registered key_object with get_key_object.

from composapy.key.models import get_key_object

get_key_object()  # KeyObject(name='a unique name', type='SqlConnectionSettings')

Search for Key

Keys can be searched by name. It returns a list of key objects.

key_objects = Key.search("same name")  # [KeyObject(name='same name', type='SqlConnectionSettings'), KeyObject(name='same name', type='StringConnectionSettings')]

QueryView

ReadTheDocs - QueryView

Create and Connect to a QueryView Driver

from composapy.queryview.api import QueryView

driver = QueryView.driver()  # you can create a driver and then connect with a key...
driver.connect(key_object)

driver = QueryView.driver(key_object)  # ... or create a driver using the key as an argument

If there is a currently registered key, there is no need to pass the key to the driver object; the driver will be automatically initialized with the database connection.

QueryView.driver()

Run a Query

df = driver.run("select * from table_name")  # returns a Pandas DataFrame of your query

SQL Magic Commands

In a notebook (IPython) environment, custom sql magic commands are loaded on import with Composapy. Note that both %sql and %%sql require a registered Session and KeyObject. Both can be loaded on import if saved in the "composapy.ini" config file. See register-session and register-key.

%sql select * from some_table  --returns the same result as QueryView.driver("select * from some_table")

Instead of doing line magic (only takes that line as input), you can alternatively do cell magic (takes entire cell as input).

select
    *
from
    some_table
where
    some_id is not null

%%sql cell magic also supports options to customize the query execution, such as setting a timeout value or capturing the output of the query into a Python variable. Run %%sql? for detailed documentation.

Run a Saved QueryView

To run a saved QueryView, you can retrieve the ID in the QueryView's URL and invoke the static run method without needing to create a driver object.

from composapy.queryview.api import QueryView

QueryView.run(123456) # returns a Pandas DataFrame with the query results

If the QueryView has literal or filter inputs, you can optionally pass values and operators to the run method using the inputs argument. This argument must be a dictionary whose keys are the display name of the input. There are two possible formats for the dictionary values, depending on the kind of input:

1. [LITERAL, FILTER] A single Python primitive (int, bool, str, float). The default operator will be used for filter inputs in this case.
2. [FILTER] A tuple of size two in the form (val, operator) where val is the primitive input value and operator is one of the acceptable QueryView input operators (=, !=, >, <, >=, <=, LIKE)

As of Composapy 0.13.1, multi-choice inputs are supported. To pass a multi-choice input, sets, tuples, and lists that contain primitive values are accepted. Due to the current nature of filter inputs requiring tuple format, literal multi-choice inputs must be passed as a tuple containing a set, tuple, or list of primitive values, like so, for instance: [{item1,item2}]. This is in order to preserve parsing logic for filter inputs, which would follow a similar format: [{item1,item2},operator].

QueryView.run(123456, inputs={"displayName1": "val1", "displayName2": (100, "!=")})

Interactive Table Output

Some operations that return Pandas DataFrames can also be run in interactive mode, which renders the data as an interactive DataTable with server-side processing for pagination. There are four main operations that can return interactive tables:

1. QueryView driver output
2. %%sql cell magic output
3. Saved QueryView output
4. Table contract output

QueryView Driver Output

When creating a QueryView driver, set interactive=True:

from composapy.queryview.api import QueryView
QueryView.driver(interactive=True)
driver.run("select * from table")

Note that unlike other QueryView driver settings, interactive can only be set when driver() is called, not when run() is called.

SQL Cell Magic Output

Simply pass the -i flag to the %%sql magic command:

select * from table

Saved QueryView Output

When running a saved QueryView, pass the interactive=True argument to run():

from composapy.queryview.api import QueryView
QueryView.run(12345, interactive=True)

Note that the paging options set in the QueryView will be respected. If no paging options are set, Auto Paging will be used.

Table Contract Output

When a table contract is passed into a DataLab (either via running a DataFlow or as a notebook runner input), it can be viewed interactively by setting a global option. The below example runs a DataFlow that contains an Odbc Query module, which has a Table output:

import composapy.interactive.options as iopts
iopts.SHOW_INTERACTIVE_TABLES = True

from composapy.dataflow.api import DataFlow
table_contract = DataFlow.run(1234).modules.get(name="Odbc Query").result.value
table_contract # NOTE: you can also use display(table_contract), but don't use print(table_contract)

Additional Information

Also, if you want to interface with pythonnet more closely, you can find our csharp documentation here.