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.
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()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)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)dataflow_run.modules[0] # Module(name='Calculator', type=Calculator)dataflow_object.modules.filter(name="calc module name")[0].inputs.first() # Input(name=Param1, type=Double, value=1.0)dataflow_run.modules.get(name="string module name").result # Result(name='foo name', type=String, value='foo value')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')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')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')]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 argumentIf 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()df = driver.run("select * from table_name") # returns a Pandas DataFrame of your queryIn 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.
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 resultsIf 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:
- [LITERAL, FILTER] A single Python primitive (
int,bool,str,float). The default operator will be used for filter inputs in this case. - [FILTER] A tuple of size two in the form
(val, operator)wherevalis the primitive input value andoperatoris one of the acceptable QueryView input operators (=, !=, >, <, >=, <=, LIKE)
Note that multi-choice inputs are not currently supported. This feature will be added in a subsequent version of Composapy.
QueryView.run(123456, inputs={"displayName1": "val1", "displayName2": (100, "!=")})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:
- QueryView driver output
%%sqlcell magic output- Saved QueryView output
- Table contract 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.
Simply pass the -i flag to the %%sql magic command:
select * from tableWhen 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.
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)Also, if you want to interface with pythonnet more closely, you can find our csharp documentation here.