Managing data sources

Describing Databases

Overview

Orbital can connect to databases to fetch data when running a query.

In order to understand the data that is present in a database table, Orbital uses a Taxi Schema for that table. The taxi schema describes the table - it’s columns and the data they hold.

Because we’re using Taxi here, the column descriptions are richer than things like String or Integer - instead using rich semantic tags like FirstName, LastName, or EmailAddress

In this guide, we’ll learn how to create a Taxi Schema for a specific database table - both through the user interface, and by directly editing a schema

Before you continue...

Before you run through this guide, make sure you’ve added a database connection for the table you want to connect.

Using the UI

The UI allows you to connect a database table directly, without having manually edit taxi files. Through the UI, Orbital will connect to the database, and create:

  • A series of types for each column in the database table
  • A model that describes the database table
  • A service that exposes query capabilities for the table

Coming soon...

At this stage, only connecting new database tables in the UI is supported. To edit and remove existing tables, modify the taxi schema directly. Support for editing via the UI will be shipped in a future release.

Before you continue...

Before you run through this guide, make sure you’ve enabled schema editing through the UI. If you’re running through one of our tutorials, don’t worry - we’ve already configured this for you.

Importing a new table

  • From the home page, click Add a data source.
    • Alternatively, Click Schema Explorer in the left-hand navigation menu, then click “Add new”
  • For the schema type to import, select “Database table”
  • Click the connection name drop-down, and select the connection for your database
    • If you haven’t yet created your connection, you can click “Add new connection”. This guide has more details.
  • Select the table from the drop-down
  • Specify a namespace for the taxi types, models and services that will be created
  • Click Create

At this stage you’ll be taken to a screen that allows you to preview the information gathered from the database table. In this form, you can click to explore the created models, types and services that were generated.

Changing the assigned types

Orbital has assigned reasonable defaults to all the fields. Specifically

  • Id’s have been tagged
  • Foreign keys have been mapped
  • For all other fields, new semantic types have been created.

If the columns in your database map to exisitng semantic types, you may wish to update the definitions. To do this:

  • Select a Model from the table on the left-hand side
  • Click on the blue link for the column you wish to change the type of
  • A search dialog is displayed
  • From here, you can search for existing types in your catalog, or create a new type

You can also click to edit documentation for any of the types, models and services.

  • Once you’re satisfied with the edits to your table and types, click Save
  • The schema has been created and written to the taxi project configured in your schema server.

Required permissions

In order to view, create or edit connected database tables through the UI, users must have the following permissions granted.

ActivityRequired permission
View the connected tablesVIEW_CONNECTIONS
Create or modify a database tableEDIT_CONNECTIONS

See (this guide)[/how-to-guides/auth/manage-user-permissions] for more information on role based security.

Editing a taxi schema

Before you continue...

Before you run through this guide, it’s worth understanding the basics of taxi, and how Orbital uses it.

Also, make sure you have a taxi project set up, and that it’s been published to Orbital. If you’re running through one of our tutorials, we’ve already taken care of this for you.

Taxi files define the mappings of data models and the services that expose them. In this guide, we’ll describe how expose a new database table to Orbital, and make it queryable.

Before starting, in your taxi project, create a new file under the src/ directory. It’s up to you what you name it. For this example, customers.taxi is a good start.

Databases, and pull-based schema definitions

As discussed in publishing schemas to Orbital, there are different ways for Orbital to consume schema information - either by data sources pushing their information directly to Orbital (well suited for application APIs), or by pulling from git-based repositories that describe the schemas.

While the push model is preferred, it’s not currently supported for databases. We’re looking into ways to embed Taxi metadata into DDL schema definitions. For now, you’ll need to maintain taxi definition file that describes the database.

Defining a table mapping

Tables are exposed to Orbital using the annotation @com.orbitalhq.jdbc.Table on a model.

Fields names in the model are expected to align with column names from the database.

Here’s an example:

import io.orbital.jdbc.Table

namespace demo.customers {

  @Table(connection = "films-database", schema = "public" , table = "customer" )
  model Customer {
    @Id // Use @Id to denote the primary key
    customerId : CustomerId
    firstName : CustomerFirstName? // Nullable columns should have the Taxi nullable symbol
    lastName : CustomerLastName
  }
}

The @Table annotation contains the following parameters:

ParameterDescription
connectionThe name of a connection, as defined in your connections configuration file.
schemaThe name of the schema. Optional, depending on your database
tableThe name of the table

Mapping the primary key

Use an @Id annotation to define the column that represents the primary key. At this stage, composite keys are not supported.

Exposing a database service

Orbital uses Taxi Services to expose query capabilities. To configure Orbital to be able to query the database table, a service must be declared, and annotated with the @com.orbitalhq.jdbc.DatabaseService annotation.

Here’s an example:

import io.vyne.jdbc.DatabaseService

namespace demo.customers {
   @DatabaseService(connection = "films-database")
   service CustomerService {
      table customers : Customer[]
   }
}

The @DatabaseService annotation contains the following parameters:

ParameterDescription
connectionThe name of a connection, as defined in your (connections configuration file)[/how-to-guides/connections/manage-database-connection/#defining-a-database-connection].

The service should expose a query operation, which returns an array of the mapped model type.

The operation should also define the query capabilities (filtering and aggregating) you wish to expose.

Coming soon...

Support for aggregations and advanced filtering in Orbital is evolving.

Not all the aggregation operations shown in the above example are fully supported yet. Feel free to reach out to us on Slack if this is something that you need.

Publishing changes to Orbital

If you’ve connected your project as a git-based project, you’ll need to commit and push your changes to the remote git repository. They’ll then be picked up om the next poll, and be available to use in Orbital.

You should now see your new types and services present in the data catalog of Orbital.

Previous
Using Protobuf
Next
Connecting a Kafka topic