---
# System prepended metadata

title: Forge.AI ANvIL Knowledge Graph v06.50
tags: [forge]

---

---
tags: forge
title: Forge.AI ANvIL Knowledge Graph v06.50 
---

# Forge.AI ANvIL Knowledge Graph v06.50 

#### Table of Content

[TOC]

# Overview

ANvIL’s semantic, temporal and probabilistic knowledge base works in concert with the other components of ANvIL to resolve all relevant data. Furthermore, in addition to supporting reasoning and inferencing tasks, ANvIL’s Knowledge Base is continually growing and learning as Forge is processing millions of documents monthly creating a source of real time compounding intelligence.

The knowledge base is also where Forge.AI maintains the various ontologies we support. An overview of the primary ontologies is listed below. It is worth noting that Forge.AI adds to the ontologies we support both organically and at the request of customers:

- Domain Specific Ontologies
- Corporate Structure
- People and Corporate Relationships
- Business Concepts
- Financial Concepts
- Marketing Concepts
- Risk (Operational Risk, Credit Risk, Market Risk)
- Science (Physics, Chemistry, Biology, etc.)
- Military and National Security
- Events
- Custom Ontologies / Ontologies as Requested

# Schema

`KG_V0640`
: The `KG_V0640` schema contains a flatten representation of some of the data contained in Forge's knowledge graph.

## Companies

The `Companies` table is a mapping table between the public and private companies URIs and their various identifiers, like tickers, CIKs, etc...

| NAME | TYPE | NULLABLE? | COMMENT |
|------|------|:---------:|---------|
| ++URI++ | VARCHAR(2048) | ✖ | Forge's globally unique identifier for the company. |
| UriPath | VARCHAR(2048) | ✖ | The `URI` stripped of the leading `http://forge.ai/company/`.<br>It allows for more optimized queries.  |
| pref_label | STRING | ✔ | The preferred label for the company. |
| parents | ARRAY(STRING) | ✖ | The UriPath (without the `http://forge.ai/company/` prefix) of the direct parent companies of this company. |
| subsidiaries | ARRAY(STRING) | ✖ | The UriPath (without the `http://forge.ai/company/` prefix ) of the subsidiaries of this company. |
| aliases | ARRAY(STRING) | ✖ | Other (older) URIs that may have been used to identify the company. |
| sector | STRING | ✔ | NAICS sector for the company. |
| website | VARCHAR(2048) | ✔ | The main web address of the company |
| tickers | ARRAY(STRING) | ✖ | The tickers of the company. |
| indices | ARRAY(STRING) | ✖ | The stock market indices the company is part of. |
| CIKs | ARRAY(INT) | ✖ | The Central Index Keys (CIK)  of the company. |
| CUSIPs | ARRAY(STRING) | ✖ | The Committee on Uniform Securities Identification Procedures numbers of the company. |
| LEIs | ARRAY(STRING) | ✖ | The Legal Entity Identifiers (LEI) of the company.  |
| SEDOLs | ARRAY(STRING) | ✖ | The Stock Exchange Daily Official List (SEDOL) identifiers of the company. |
| ISINs | ARRAY(STRING) | ✖ | The International Securities Identification Numbers (ISIN) of the company. |
| NAICS | ARRAY(INT) | ✖ | The North American Industry Classification System code of the company. |
| PERMIDs | ARRAY(INT) | ✖ | The Refinitiv Permanent Identifiers, or PermIDs, of the company. |

##### Notes:

1. The `ARRAY` fields in this table will not be `NULL` but may be empty.
For example, here's a way to list companies without a ticker:
```sql=
SELECT *
FROM Companies
WHERE ARRAY_SIZE(tickers) = 0
```
2. It might be convinient to flatten `ARRAY`s as follows:

```sql=
WITH cusips AS (
    SELECT c.uri,
           cusip.value::string AS cusip
    FROM Companies c,
         LATERAL FLATTEN( INPUT => c.cusips ) cusip
)
```
3. `CUSIPs` may contain the CUSIP-6, or CUSIP-8 if a full CUSIP is not known.
4. Compared to the [`EntityProperties`](https://hackmd.io/b28HhKSOT8mmxX2Rj9_CHg#EntityProperties) table, this table contains more identifiers and the latest snapshot of the knowledge graph data.

##### Ticker format

Tickers adhere to the following pattern: `[country code (iso-3166-2)]:[exchange]:[symbol]`.
For example, Apple Inc.'s ticker on the NASDAQ is: `US:NASDAQ:AAPL`

It might be convinient to flatten the tickers as follows:

```sql=
WITH tickers AS (
    SELECT c.uri,
           ticker.value::string AS ticker,
           SPLIT_PART(ticker, ':', 1) AS country,
           SPLIT_PART(ticker, ':', 2) AS exchange,
           SPLIT_PART(ticker, ':', 3) AS symbol
    FROM Companies c,
         LATERAL FLATTEN( INPUT => c.tickers ) ticker
)
```

###### Current List of exchanges

| Name | Ticker Attribute Prefix |
|------|-------------------------|
| Abu Dhabi Securities Exchange | AE:ADSM: |
| Buenos Aires Stock Exchange | AR:MVB: |
| Vienna Stock Exchange | AT:VSE: |
| Sydney Stock Exchange | AU:ASX: |
| Euronext Brussels | BE:ENB: |
| BM&F Bovespa - Securities, Commodities and Futures Exchange | BR:BVSP: |
| TSX Venture Exchange | CA:CVE: |
| Toronto Stock Exchange | CA:TSX: |
| Six Swiss Exchange | CH:SSX: |
| Santiago Stock Exchange | CL:BCS: |
| Shanghai Stock Exchange | CN:SGSE: |
| Shenzhen Stock Exchange | CN:SSE: |
| Colombian Stock Exchange | CO:BVC: |
| Prague Stock Exchange | CZ:PSE: |
| Frankfurt Stock Exchange | DE:FSX: |
| Munich Stock Exchange | DE:MSE: |
| Stuttgart Stock Exchange | DE:SSE: |
| Nasdaq Nordic Copenhagen | DK:CSE: |
| Nasdaq Baltic Tallinn | EE:TSE: |
| Barcelona Stock Exchange | ES:BSE: |
| Nasdaq Nordic Helsinki | FI:HSE: |
| Euronext Paris | FR:PEN: |
| London Stock Exchange | GB:LSE: |
| Athens Exchange | GR:ASE: |
| Hong Kong Stock Exchange | HK:SEHK: |
| Budapest Stock Exchange | HU:BSE: |
| Indonesian Stock Exchange | ID:IDX: |
| Irish Stock Exchange | IE:ISE: |
| BSE Ltd. | IN:BSE: |
| National Stock Exchange of India | IN:NSE: |
| Italian Exchange | IT:MSE: |
| Tokyo Stock Exchange | JP:TSE: |
| KOSDAQ | KR:KSDQ: |
| Korea Stock Exchange | KR:KSE: |
| Nasdaq Baltic Vilnius | LT:NSE: |
| Luxembourg Stock Exchange | LU:LSE: |
| Mexican Stock Exchange | MX:MSE: |
| Malaysian Exchange | MY:KLSE: |
| Nigerian Stock Exchange | NG:NSE: |
| Euronext Amsterdam | NL:ENA: |
| Oslo Bors | NO:OB: |
| New Zealand Unlisted Bond | NZ:BND: |
| Lima Stock Exchange | PE:BVL: |
| Philippine Stock Exchange | PH:PSE: |
| Warsaw Stock Exchange | PL:WSE: |
| Euronext Lisbon | PT:BVL: |
| Moscow Stock Exchange | RU:MSE: |
| Nasdaq Nordic Stockholm | SE:SSE: |
| Singapore Exchange | SG:SSE: |
| Bratislava Stock Exchange | SK:BSE: |
| Stock Exchange of Thailand | TH:SET: |
| Taiwan Stock Exchange | TW:TSE: |
| NYSE MKT | US:AMEX: |
| BATS US Stock Exchange | US:BATS: |
| USA Unlisted Bond | US:BND: |
| Chicago Stock Exchange | US:CHI: |
| National Stock Exchange (USA) | US:CIN: |
| Finra OTCBB | IS:FNBB: |
| NASDAQ Stock Markets | US:NASDAQ: |
| New York Stock Exchange | US:NYSE: |
| NYSE Arca | US:PAC: |
| Nasdaq OMX Philadelphia | US:PHIL: |
| US Portal Exchange | US:PORT: |
| Pink Sheets Grey Market | US:PSGM: |
| FINRA TRACE | US:TRCE: |
| Johannesburg Stock Exchange | ZA:JSE: |

###### Current List of stock market indices

| Name | Symbol |
|------|--------|
| NASDAQ Composite | IXIC |
| NASDAQ-100 | NDX |
| Dow Jones Industrial Average | DJIA |
| Russell 3000 | RUA |
| Russell 1000 | RUI |
| S&P 500 (GSPC, INX, SPX) | SPX |
| S&P/TSX 60 | TX60 |
| Nikkei 225 | N225 |
| CAC 40 | FHCI |
| DAX- 30 companies weighted by the market cap | DAX |
| IBEX 35 | IB |
| FTSE 100 Index | UKX |
| S&P/ASX 300 | XKO |

#### Use-Cases

##### Find a company URI from its CIK code

```sql=
SELECT uri
FROM Companies
WHERE ARRAY_CONTAINS(320193, ciks)
```

or

```sql=
SELECT c.uri
FROM Companies c,
     LATERAL FLATTEN( INPUT => c.ciks ) cik
WHERE cik.value = 320193
```

##### Find a company URI from its CUSIP number

```sql=
SELECT c.uri
FROM Companies c,
     LATERAL FLATTEN( INPUT => c.cusips ) cusip
WHERE cusip.value ILIKE '03783310%'
```

##### Find a company URI from its stock symbol

```sql=
SELECT DISTINCT c.uri
FROM Companies c,
     LATERAL FLATTEN( INPUT => c.tickers ) ticker
WHERE ticker.value LIKE '%:AAPL'
```

##### List all the companies listed on the NASDAQ

```sql=
SELECT DISTINCT c.uri, c.pref_label
FROM Companies c,
     LATERAL FLATTEN( INPUT => c.tickers ) ticker
WHERE ticker.value LIKE 'US:NASDAQ:%'
```

##### Map CIKs to CUSIPs

Knowing a CIK what are the CUSIPs associated to that company?

```sql=
SELECT DISTINCT cusip.value::STRING AS cusip
FROM Companies c,
     LATERAL FLATTEN( INPUT => c.ciks ) cik,
     LATERAL FLATTEN( INPUT => c.cusips ) cusip
WHERE cik.value = 320193
```

##### Find the CIKs for all the parent companies (recursively) of a CUSIP

```sql=
WITH RECURSIVE uripaths AS (
    SELECT c.uripath
    FROM Companies c,
         LATERAL FLATTEN( INPUT => c.cusips ) cusip
    WHERE cusip.value = '40427lab0'
   
    UNION ALL SELECT c.uripath
    FROM Companies c
    INNER JOIN uripaths
    WHERE ARRAY_CONTAINS(uripaths.uripath::VARIANT, c.subsidiaries)
)

SELECT ciks.value AS cik
FROM Companies c,
     LATERAL FLATTEN( INPUT => c.ciks ) ciks
WHERE c.uripath IN (SELECT uripath FROM uripaths)
```

## URIChanges

From time to time, companies URI may changed. For example, two companies may merge. The `URIChanges` table keeps track of those changes in URIs.

| NAME | TYPE | NULLABLE? | COMMENT |
|------|------|:---------:|---------|
| new_uri | VARCHAR(2048) | ✖ | The new URI. |
| old_uri | VARCHAR(2048) | ✖ | The _old_/replaced URI. |
| correction_date | DATETIME | ✖ | Date of the replacement. |

##### Notes:

1. `correction_time` may be used as a versioning scheme for the table.

## Locations

The `Locations` table exposes the geographical location vetices from the knowledge graph.

| NAME | TYPE | NULLABLE? | COMMENT |
|------|------|:---------:|---------|
| ++uri++ | VARCHAR(2048) | ✖ | Forge's globally unique identifier for the location. |
| type | VARCHAR(2048) | ✔ | The type of the location.   |
| pref_label | STRING | ✔ | The preferred label for the location. |
| iso3166_1 | CHAR(2) | ✔ | The [ISO-3166-1](https://en.wikipedia.org/wiki/ISO_3166-1) code for this location. |
| iso3166_2 | STRING | ✔ | The [ISO-3166-2](https://en.wikipedia.org/wiki/ISO_3166-2) code for this location.
| geonameid | INT | ✔ | The [GeoName](https://www.geonames.org/)'s identifier for this location. |
| latitude | FLOAT | ✔ | The latitude of the centroid for this location. |
| longitude | FLOAT | ✔ | The longitude of the centroid for this location. |
| country_uri | VARCHAR(2048) | ✔ | The URI of the country this location belongs to |
| adm1_uri | VARCHAR(2048) | ✔ | The URI of the ADM1 feature (ie. a primary administrative division of a country, such as a state in the United States) this locations belongs to. |

##### Notes:

1. `iso3166_1` is only populated for locations that represent a _country_.
The `country_uri` column contains the URI of the _country_ the location belongs to.
For example: The city of Cambridge, MA (USA)(`http://forge.ai/location/city/us/ma/cambridge`) will have `NULL` as `iso3166_1` but `http://www.omg.org/spec/lcc/countries/iso3166-1-countrycodes/unitedstates` as `country_uri`.

2. Similarly, `iso3166_2` is only populated for locations that represent a _principal subdivisions_ (e.g., provinces or states).
The `adm1_uri` column contains the URI of the _subdivision_ the location belongs to.
For example: The city of Cambridge, MA (USA)(`http://forge.ai/location/city/us/ma/cambridge`) will have `NULL` as `iso3166_1` and NULL as `iso3166_2` but `http://www.omg.org/spec/lcc/countries/iso3166-1-countrycodes/unitedstates` as `country_uri` and `http://www.omg.org/spec/lcc/countries/regions/iso3166-2-subdivisioncodes-us/massachusetts` as `adm1_uri`.

#### Use-Cases

##### Find all locations from the US

1. From the URI of the country (`http://www.omg.org/spec/lcc/countries/iso3166-1-countrycodes/unitedstates`)

```sql=
select uri
from Locations
where country_uri = 'http://www.omg.org/spec/lcc/countries/iso3166-1-countrycodes/unitedstates'
```

2. From the ISO-3166-1 code of the country (US)

```sql=
select l.uri
from Locations l
join Locations country on country.uri = l.country_uri
where country.iso3166_1 = 'US'
```

# Revision History

## 06.50

* Added `Locations` table.
* Added `aliases` column to the `Companies` table.

## 06.40
 
First release.
 
# Useful links

* [Forge.AI ANvIL Reference](https://hackmd.io/@forgeai/Sy7CTSouU)
* [Snowflake Documentation](https://docs.snowflake.net)
  * [Connecting to Snowflake](https://docs.snowflake.net/manuals/user-guide-connecting.html)
