Peering DBの情報をAPIをつかって取得する

# Peering DBからAPI経由で情報を抜き出して、Power BIで可視化したい
Power BIでもTableauでも、可視化や分析にはデータがないとなにもできません。
ということで今回はだれでもアクセスできるPeering DBの可視化をします。
Power BIで～と書きましたが、長くなってしまったためPower BI部分は別の記事で書きます。
この記事は主にPeering DBのAPIについてです。
Pythonを使ってます

## Agenda
1. Peering DBとは
1. 軽く触ってみる
1. 目的別コード置き場
1. 参考資料

## １．Peering DBとは
>AS番号を持つ組織のネットワーク同士が相互接続することを、PeerやPeeringと呼ばれています。具体的にはBGPというプロトコルを両組織のルータで動作させ、お互いの組織のIPアドレスやAS番号を設定することでPeeringを実現します。
https://qiita.com/taijijiji/items/a8a8beb68038a993ac2e


## ２．軽く触ってみる
`https://peeringdb.com/api/`
Peering DBのリンクへとりあえずリクエストしてみる。
これから先のクエリを与えたりアクセス先がかわっても、この`data`と`meta`の二つの要素に分かれているので、
基本はここから`data`だけを抜き出すことになる

コード

```python
#! /usr/bin/env python
# -*- coding: utf-8 -*-
import requests
import json
import pandas as pd
def get_net_list():
    pdb_url = 'https://peeringdb.com/api/'
    r = requests.get(pdb_url)
    origin = r.json()
    return origin

if __name__ == '__main__' :
    exp = get_net_list()
```


結果

```json
{
  "data": [
    {
      "fac": "https://peeringdb.com/api/fac",
      "ix": "https://peeringdb.com/api/ix",
      "ixfac": "https://peeringdb.com/api/ixfac",
      "ixlan": "https://peeringdb.com/api/ixlan",
      "ixpfx": "https://peeringdb.com/api/ixpfx",
      "net": "https://peeringdb.com/api/net",
      "poc": "https://peeringdb.com/api/poc",
      "netfac": "https://peeringdb.com/api/netfac",
      "netixlan": "https://peeringdb.com/api/netixlan",
      "org": "https://peeringdb.com/api/org",
      "as_set": "https://peeringdb.com/api/as_set"
    }
  ],
  "meta": {}
}
```

### それぞれのリンクでは各リストが出力できる
#### facility list
`https://peeringdb.com/api/fac`
Peering DBに登録されているデータセンターなどのファシリティの一覧はここから一覧が取得できそう。
緯度経度もあるが、入っているところもあればないところもあるので、ここはひとつ課題。


```json
{
  "data": [
    {
      "id": 1,
      "org_id": 2,
      "org_name": "Equinix, Inc.",
      "name": "Equinix DC1-DC15 - Ashburn",
      "aka": "",
      "name_long": "",
      "website": "http://www.equinix.com/",
      "clli": "ASBNVA",
      "rencode": "",
      "npanxx": "703-723",
      "notes": "",
      "net_count": 389,
      "ix_count": 4,
      "sales_email": "",
      "sales_phone": "",
      "tech_email": "",
      "tech_phone": "",
      "created": "2010-07-29T00:00:00Z",
      "updated": "2020-08-26T05:23:01Z",
      "status": "ok",
      "address1": "21715 Filigree Ct",
      "address2": "Building F",
      "city": "Ashburn",
      "country": "US",
      "state": "VA",
      "zipcode": "20147-6205",
      "floor": "",
      "suite": "",
      "latitude": 39.016363,
      "longitude": -77.459023
    },
    {
      "id": 2,
      "org_id": 8592,
      "org_name": "Digital Realty",
      "name": "Digital Realty SFO (200 Paul)",
      "aka": "",
      "name_long": "",
      "website": "http://www.digitalrealty.com",
      "clli": "SNFCCA",
      "rencode": "",
      "npanxx": "415-822",
      "notes": "",
      "net_count": 39,
      "ix_count": 2,
      "sales_email": "",
      "sales_phone": "",
      "tech_email": "",
      "tech_phone": "",
      "created": "2010-07-29T00:00:00Z",
      "updated": "2019-04-09T04:46:43Z",
      "status": "ok",
      "address1": "200 Paul Ave",
      "address2": "",
      "city": "San Francisco",
      "country": "US",
      "state": "CA",
      "zipcode": "94110",
      "floor": "",
      "suite": "",
      "latitude": 37.723407,
      "longitude": -122.397923
    },
    {
      "id": 4,
      "org_id": 2,
      "org_name": "Equinix, Inc.",
      "name": "Equinix DA1 - Dallas",
      "aka": "",
      "name_long": "",
      "website": "https://www.equinix.com/locations/americas-colocation/united-states-colocation/dallas-data-centers/da1/",
      "clli": "DLLSTX",
      "rencode": "",
      "npanxx": "214-782",
      "notes": "",
      "net_count": 128,
      "ix_count": 2,
      "sales_email": "",
      "sales_phone": "",
      "tech_email": "",
      "tech_phone": "",
      "created": "2010-07-29T00:00:00Z",
      "updated": "2020-08-26T05:23:01Z",
      "status": "ok",
      "address1": "1950 N Stemmons Fwy",
      "address2": "Ste 1034",
      "city": "Dallas",
      "country": "US",
      "state": "TX",
      "zipcode": "75207-3137",
      "floor": "",
      "suite": "",
      "latitude": 32.800955,
      "longitude": -96.81955
    },
    {
      "id": 5,
      "org_id": 2,
      "org_name": "Equinix, Inc.",
      "name": "Equinix SV8 - Silicon Valley, Palo Alto",
      "aka": "",
      "name_long": "",
      "website": "https://www.equinix.com/locations/americas-colocation/united-states-colocation/silicon-valley-data-centers/sv8/",
      "clli": "PLALCA",
      "rencode": "",
      "npanxx": "650-617",
      "notes": "",
      "net_count": 82,
      "ix_count": 4,
      "sales_email": "",
      "sales_phone": "",
      "tech_email": "",
      "tech_phone": "",
      "created": "2010-07-29T00:00:00Z",
      "updated": "2020-08-26T05:23:01Z",
      "status": "ok",
      "address1": "529 Bryant St",
      "address2": "",
      "city": "Palo Alto",
      "country": "US",
      "state": "CA",
      "zipcode": "94301-1704",
      "floor": "",
      "suite": "",
      "latitude": 37.445896,
      "longitude": -122.160775
    },
.........
```

#### ix list
`https://peeringdb.com/api/ix`
IXのリストが取得できそう

```json
{
  "data": [
    {
      "id": 1,
      "org_id": 2,
      "name": "Equinix Ashburn",
      "aka": "",
      "name_long": "Equinix Internet Exchange Ashburn",
      "city": "Ashburn",
      "country": "US",
      "region_continent": "North America",
      "media": "Ethernet",
      "notes": "",
      "proto_unicast": true,
      "proto_multicast": false,
      "proto_ipv6": true,
      "website": "https://ix.equinix.com",
      "url_stats": "",
      "tech_email": "support@equinix.com",
      "tech_phone": "",
      "policy_email": "support@equinix.com",
      "policy_phone": "",
      "net_count": 333,
      "fac_count": 2,
      "ixf_net_count": 0,
      "ixf_last_import": null,
      "service_level": "Not Disclosed",
      "terms": "Not Disclosed",
      "created": "2010-07-29T00:00:00Z",
      "updated": "2020-12-07T17:31:45Z",
      "status": "ok"
    },
    {
      "id": 2,
      "org_id": 2,
      "name": "Equinix Chicago",
      "aka": "",
      "name_long": "Equinix Internet Exchange Chicago",
      "city": "Chicago",
      "country": "US",
      "region_continent": "North America",
      "media": "Ethernet",
      "notes": "",
      "proto_unicast": true,
      "proto_multicast": false,
      "proto_ipv6": true,
      "website": "https://ix.equinix.com",
      "url_stats": "",
      "tech_email": "support@equinix.com",
      "tech_phone": "",
      "policy_email": "support@equinix.com",
      "policy_phone": "",
      "net_count": 247,
      "fac_count": 3,
      "ixf_net_count": 0,
      "ixf_last_import": null,
      "service_level": "Not Disclosed",
      "terms": "Not Disclosed",
      "created": "2010-07-29T00:00:00Z",
      "updated": "2020-12-08T14:58:33Z",
      "status": "ok"
    },
    {
      "id": 3,
      "org_id": 2,
      "name": "Equinix Dallas",
      "aka": "",
      "name_long": "Equinix Internet Exchange Dallas",
      "city": "Dallas",
      "country": "US",
      "region_continent": "North America",
      "media": "Ethernet",
      "notes": "",
      "proto_unicast": true,
      "proto_multicast": false,
      "proto_ipv6": true,
      "website": "https://ix.equinix.com",
      "url_stats": "",
      "tech_email": "support@equinix.com",
      "tech_phone": "",
      "policy_email": "support@equinix.com",
      "policy_phone": "",
      "net_count": 177,
      "fac_count": 6,
      "ixf_net_count": 0,
      "ixf_last_import": null,
      "service_level": "Not Disclosed",
      "terms": "Not Disclosed",
      "created": "2010-07-29T00:00:00Z",
      "updated": "2020-12-08T14:58:51Z",
      "status": "ok"
    },
..........
```

#### ここから先は長くなりそうなのでテキストで内容を説明

`ixfac": "https://peeringdb.com/api/ixfac`
どこのIXがどのFacilityに入っているのかを紐づけ。ID情報だけなのでコールして1秒強くらいで結果が返ってくる

`ixlan": "https://peeringdb.com/api/ixlan`
MTUなどのLANに関する基礎情報

`ixpfx": "https://peeringdb.com/api/ixpfx`
prefixなどのそのIXに関する基礎情報

`net": "https://peeringdb.com/api/net`
CSPやNSP、ISPなどのサービスプロバイダー情報。コールして15秒くらい取得にかかる
org_id

`poc": "https://peeringdb.com/api/poc`
Point of contact,つまり担当者情報

`netfac": "https://peeringdb.com/api/netfac`
プロバイダーとFacility情報を紐づけるテーブル
net_id,fac_id

`org": "https://peeringdb.com/api/org`
PeeringDBに登録されている組織の一覧と基礎情報

`netixlan": "https://peeringdb.com/api/netixlan`
プロバイダーのLanなどに関する情報

`as_set": "https://peeringdb.com/api/as_set`
ASの番号と名称の組み合わせ

#### 各エンドポイントでは、一般的なAPI同様パラメータを指定してコールもできる
・Amazon.comで試してみる
以下のURLでコールする
`pdb_url = 'https://peeringdb.com/api/net?name=Amazon.com'`

結果

```json
{
  "data": [
    {
      "id": 1418,
      "org_id": 1520,
      "name": "Amazon.com",
      "aka": "Amazon Web Services",
      "name_long": "",
      "website": "http://www.amazon.com",
      "asn": 16509,
      "looking_glass": "",
      "route_server": "",
      "irr_as_set": "AS-AMAZON",
      "info_type": "Enterprise",
      "info_prefixes4": 5000,
      "info_prefixes6": 2000,
      "info_traffic": "",
      "info_ratio": "Balanced",
      "info_scope": "Global",
      "info_unicast": true,
      "info_multicast": false,
      "info_ipv6": true,
      "info_never_via_route_servers": false,
      "ix_count": 125,
      "fac_count": 131,
      "notes": "AWS Peering: https://www.peering.aws\n\n\n\nPeering requests:\n\nWhen submitting a peering request, please address the specific regional contact listed below for the location of your request (i.e. peering requests for London should use peering-emea@amazon.com while peering requests for Singapore should use peering-apac@amazon.com). This will ensure your request is processed and addressed in a timely fashion. Please do not copy contacts not meant for peering policy in the location of your request.\n\n-----\n\nOperational issues:\n\nIf you experience connectivity issues to Amazon, please first visit: http://ec2-reachability.amazonaws.com/.\nWhen contacting our Operational alias below, please include trace/debug information (source and destination address) and details for the affected prefixes. This will reduce troubleshooting time. Please do not contact policy addresses for operational support.\n\n----\n\nInformational:\n\nFor details about our network, routing and interconnection options, please see https://peering.aws.\nA list of our IP ranges with source location information is maintained here: https://ip-ranges.amazonaws.com/ip-ranges.json.\nPlease use the Maintenance alias to notify us regarding maintenance activities on your end and do not copy other aliases into these inquiries.  \n\n----\n\nRPKI:\n\nFrom December 2020, AWS will drop all routes deemed to be RPKI invalid on incoming eBGP sessions.",
      "netixlan_updated": "2021-06-29T10:26:47.080924Z",
      "netfac_updated": "2021-03-17T00:40:10.946138Z",
      "poc_updated": "2020-12-01T12:29:55Z",
      "policy_url": "https://aws.amazon.com/peering/policy",
      "policy_general": "Selective",
      "policy_locations": "Preferred",
      "policy_ratio": false,
      "policy_contracts": "Not Required",
      "allow_ixp_update": false,
      "created": "2007-12-20T16:39:54Z",
      "updated": "2021-05-04T11:44:54Z",
      "status": "ok"
    }
  ],
  "meta": {}
}
```

#### 同じAmazon.comでもパラメーターの`name`で指定した時とURLでIdで指定した時とは結果が異なるので必要に応じて使い分けるとよい
以下のURLでコールする
`pdb_url = 'https://peeringdb.com/api/net/1418'`
1418はAmazon.comのId

結果
収容されているFacilityも一緒に出力される

```json
{
  "data": [
    {
      "id": 1418,
      "org_id": 1520,
      "org": {
        "id": 1520,
        "name": "Amazon.com",
        "aka": "",
        "name_long": "",
        "website": "",
        "notes": "",
        "net_set": [
          1418
        ],
        "fac_set": [
          8242,
          8243
        ],
        "ix_set": [],
        "address1": "",
        "address2": "",
        "city": "",
        "country": "",
        "state": "",
        "zipcode": "",
        "floor": "",
        "suite": "",
        "latitude": null,
        "longitude": null,
        "created": "2007-12-20T16:39:54Z",
        "updated": "2016-03-14T21:58:43Z",
        "status": "ok"
      },
      "name": "Amazon.com",
      "aka": "Amazon Web Services",
      "name_long": "",
      "website": "http://www.amazon.com",
      "asn": 16509,
      "looking_glass": "",
      "route_server": "",
      "irr_as_set": "AS-AMAZON",
      "info_type": "Enterprise",
      "info_prefixes4": 5000,
      "info_prefixes6": 2000,
      "info_traffic": "",
      "info_ratio": "Balanced",
      "info_scope": "Global",
      "info_unicast": true,
      "info_multicast": false,
      "info_ipv6": true,
      "info_never_via_route_servers": false,
      "ix_count": 125,
      "fac_count": 131,
      "notes": "AWS Peering: https://www.peering.aws\n\n\n\nPeering requests:\n\nWhen submitting a peering request, please address the specific regional contact listed below for the location of your request (i.e. peering requests for London should use peering-emea@amazon.com while peering requests for Singapore should use peering-apac@amazon.com). This will ensure your request is processed and addressed in a timely fashion. Please do not copy contacts not meant for peering policy in the location of your request.\n\n-----\n\nOperational issues:\n\nIf you experience connectivity issues to Amazon, please first visit: http://ec2-reachability.amazonaws.com/.\nWhen contacting our Operational alias below, please include trace/debug information (source and destination address) and details for the affected prefixes. This will reduce troubleshooting time. Please do not contact policy addresses for operational support.\n\n----\n\nInformational:\n\nFor details about our network, routing and interconnection options, please see https://peering.aws.\nA list of our IP ranges with source location information is maintained here: https://ip-ranges.amazonaws.com/ip-ranges.json.\nPlease use the Maintenance alias to notify us regarding maintenance activities on your end and do not copy other aliases into these inquiries.  \n\n----\n\nRPKI:\n\nFrom December 2020, AWS will drop all routes deemed to be RPKI invalid on incoming eBGP sessions.",
      "netixlan_updated": "2021-06-29T10:26:47.080924Z",
      "netfac_updated": "2021-03-17T00:40:10.946138Z",
      "poc_updated": "2020-12-01T12:29:55Z",
      "policy_url": "https://aws.amazon.com/peering/policy",
      "policy_general": "Selective",
      "policy_locations": "Preferred",
      "policy_ratio": false,
      "policy_contracts": "Not Required",
      "netfac_set": [
        {
          "id": 3213,
          "name": "Equinix DC1-DC15 - Ashburn",
          "city": "Ashburn",
          "country": "US",
          "fac_id": 1,
          "local_asn": 16509,
          "created": "2010-07-29T00:00:00Z",
          "updated": "2016-03-11T07:20:53Z",
          "status": "ok"
        },
        {
          "id": 3814,
          "name": "Equinix SV8 - Silicon Valley, Palo Alto",
          "city": "Palo Alto",
          "country": "US",
          "fac_id": 5,
          "local_asn": 16509,
          "created": "2010-07-29T00:00:00Z",
          "updated": "2016-03-14T20:55:02Z",
          "status": "ok"
        },
        {
          "id": 5531,
          "name": "Equinix SV1/SV5/SV10 - Silicon Valley, San Jose",
          "city": "San Jose",
          "country": "US",
          "fac_id": 6,
          "local_asn": 16509,
          "created": "2010-07-29T00:00:00Z",
          "updated": "2016-03-14T20:45:50Z",
          "status": "ok"
        },
```

### ３．目的別コード
もともとはPeering DBから指定のIXはどこに入っているのか、そもそもロケーションってどれくらいあるのだろうとかを出力したかったが、
難しく考えずに必要なリストを呼び出してKeyIDでリレーションを組めばよさそうなことに気づいた。（当たり前か）
例えばCSPでフィルタしてロケーションを紐解こうとすると、`~/net/Id`で毎回コールする必要があるかと思ったらそうではなくて、
`~/netfac`をつかってそれぞれプロバイダーとFacilityのリストをリレーション組むだけでよかった。

#### csvへFacilityのリストを出力する

```python
#! /usr/bin/env python
# -*- coding: utf-8 -*-
import requests
import json
import pandas as pd
def get_peeringdb():
    pdb_url = 'https://peeringdb.com/api/fac'
    r = requests.get(pdb_url)
    origin = r.json()
    # Peering DBのリクエストへの結果でもあったように、meta部分は除いてデータフレームへ落とし込む
    df = pd.json_normalize(origin['data'])
    # csvへ出力
    csv_data = df.to_csv('facilityList.csv')
    return origin, df

if __name__ == '__main__' :
    # get data center list
    fac_list = get_peeringdb()
```
あとはURLを変えて取得するだけ。
可視化自体はPower BIでやりたかったので、
これらを使ってPeering DBの可視化をやっていく。

４．参考
Peering DB web page
https://www.peeringdb.com/

Peering DB Api document
https://www.peeringdb.com/apidocs/