# Python Microsoft Fabric - Looking up Fabric data from your services

Copy names from Fabric tables to the cache on a schedule and read them from there while processing messages.

A service that processes messages often gets IDs in each message and needs the names for them. When the names are in Fabric tables, a query per message is too slow, because a lakehouse query runs on Spark and takes seconds, and a service may get a thousand messages a minute. On this page, one service copies the tables to the cache every five minutes, and another service reads the names from the cache.

> **What you need from your Fabric admin**
>
> The workspace ID and the lakehouse ID, from the address bar when the lakehouse is open, and the Livy endpoint tenant setting that lets apps run queries, under Developer settings in the Fabric admin portal.

## The tables in Fabric {#the-tables-in-fabric}

The service below copies two tables into the cache, `locations` and `insurers`, each with an ID column and a name column:

## The refresh service {#the-refresh-service}

The service runs one query per table and stores each table in the cache as a JSON map from ID to name. It runs from the scheduler every five minutes, and the cache entries expire after ten, so if one refresh fails, the names from the previous one are still in the cache.

```python
# -*- coding: utf-8 -*-

# stdlib
from json import dumps

# Zato
from zato.server.service import Service

# Which table feeds which cache key, and which columns are the ID and the name
_lookups = {
    'lookups:locations': ('locations', 'location_id', 'name'),
    'lookups:insurers':  ('insurers',  'insurer_id',  'name'),
}

_cache_ttl = 600

class RefreshLookups(Service):

    name = 'lookups.refresh'

    def handle(self):

        workspace_id = '11111111-2222-3333-4444-555555555555'
        lakehouse_id = '66666666-7777-8888-9999-000000000000'

        conn = self.microsoft.fabric['My Fabric']

        counts = {}

        for cache_key, (table_name, id_column, name_column) in _lookups.items():

            # Read the table ..
            sql = f'select {id_column}, {name_column} from {table_name}'
            rows = conn.query(workspace_id, lakehouse_id, sql)

            # .. turn it into a map from ID to name ..
            names = {}
            for row in rows:
                names[row[id_column]] = row[name_column]

            # .. and keep it in the cache for ten minutes.
            data = dumps(names)
            self.cache.set(cache_key, data, ex=_cache_ttl)
            counts[table_name] = len(names)

        self.response.payload = counts
```

To schedule the service, go to `Scheduler → Config` in the Dashboard, click "Create a new job" and pick the interval-based type with `5` minutes and `lookups.refresh` as the service.

After the first run, the cache has the two entries:

## The lookup in a message service {#the-lookup-in-a-message-service}

A service that needs a name reads it from the cache. This one gets a message with two IDs and looks up the name for each:

```python
# -*- coding: utf-8 -*-

# stdlib
from json import loads

# Zato
from zato.server.service import Service

class ProcessInvoice(Service):

    name = 'invoices.process'
    input = 'invoice_id', 'location_id', 'insurer_id', 'amount'

    def handle(self):

        invoice = self.request.input

        # The names from the last refresh.
        cached_locations = self.cache.get('lookups:locations')
        cached_insurers = self.cache.get('lookups:insurers')

        locations = loads(cached_locations)
        insurers = loads(cached_insurers)

        invoice_id = invoice.invoice_id
        amount = invoice.amount
        location_name = locations[invoice.location_id]
        insurer_name = insurers[invoice.insurer_id]

        self.logger.info(f'Invoice {invoice_id} -> {location_name}, {insurer_name}, {amount}')
```

A message with `INV-3310`, `LOC-01` and `INS-01` logs `Invoice INV-3310 -> Riverside, Cascade Health Plan, 2275.00`, without a Spark query.

## See also {#see-also}

- [Keeping lookup tables up to date](https://zato.io/docs/dev/examples/cloud/fabric/lookup-tables.html) - Writing the lookup tables to Fabric
- [Building an API on top of Fabric data](https://zato.io/docs/dev/examples/cloud/fabric/api-on-fabric-data.html) - Caching the results of a query that REST callers run
- [Queries](https://zato.io/docs/dev/examples/cloud/fabric/api/queries.html) - query and Spark sessions in detail

## Learn more {#learn-more}

- [Development documentation](https://zato.io/docs/dev/) - Everything about writing services, in one place
- [Requests and responses](https://zato.io/docs/dev/request-response/) - What a service receives, what it returns and how to shape both
- [Integration examples](https://zato.io/docs/dev/examples/) - Ready-made code for the systems you are likely to connect to
- [IDE and debugging](https://zato.io/docs/dev/ide/) - Write services in the Dashboard or in your own editor
- [Data models](https://zato.io/docs/dev/model/) - Declare inputs and outputs and have them validated for you
- [In-depth API tutorial](https://zato.io/tutorials/main/01.html) - The full platform tutorial, from installation to production patterns