米国の固定ブロードバンド接続について:2018年12月FCC統計

米国の固定ブロードバンド接続について、前回より新しいデータが公開されたので早速Postgressにデータをインポートしてみました。データは、前回と同じく、こちらから入手しました。（データは2018年12月時点のデータで、ダウンロードリンクはここです。）

データサイズは前回と同じく10GBほどで、あまり変わりません。すでにブロードバンド接続は接続数で見たときに、頭打ちであるということですが、前回クエリした結果と同じく、接続速度の改善がどの程度あるのか興味のあるところです。

なお今回のデータは、head -n 1でヘッダーを見たところ、前回のヘッダーの省略名のようでしたが、項目自体は変わりないようでした。
前回のヘッダ

$ head -n1 Fixed_Broadband_Deployment_Data__June__2017_Status_V1.csv
Logical Record Number,Provider ID,FRN,Provider Name,DBA Name,Holding Company Name,Holding Company Number,Holding Company Final,State,Census Block FIPS Code,Technology Code,Consumer,Max Advertised Downstream Speed (mbps),Max Advertised Upstream Speed (mbps),Business,Max CIR Downstream Speed (mbps),Max CIR Upstream Speed (mbps)

今回のヘッダ

$ head -n 1 fbd_us_with_satellite_dec2018_v2.csv
LogRecNo,Provider_Id,FRN,ProviderName,DBAName,HoldingCompanyName,HocoNum,HocoFinal,StateAbbr,BlockCode,TechCode,Consumer,MaxAdDown,MaxAdUp,Business,MaxCIRDown,MaxCIRUp

なお、今回、データベースをUTF8で作成したいので、PostgresはDebian10/busterにインストールしました。(windowsではdbのロケールはUTF-8に設定できません。また、windowsでのcopyコマンドが32bitのため、データ分割など手間がかかるので、linuxで作成しています。)

1. databaseの作成。
今回のデータベースは、usfixedbbユーザがオーナーで、エンコーディングはUTF-8、ロケールはja_JP.UTF-8にてDBを作成します。

$ sudo apt-get install postgres
$ sudo -u postgres -i

$ psql
psql (11.7 (Debian 11.7-0+deb10u1))
"help" でヘルプを表示します。

postgres=# create user usfixedbb WITH PASSWORD 'Password' CREATEDB;
CREATE ROLE
postgres=# create database usfixedbb with OWNER=usfixedbb ENCODING='UTF-8' LC_COLLATE='ja_JP.UTF-8' LC_CTYPE='ja_JP.UTF-8';
CREATE DATABASE
postgres=# GRANT pg_read_server_files TO usfixedbb;
GRANT ROLE
postgres=# \q

2.テーブルの作成とデータのインポート
先に、ダウンロードしたデータを/tmp以下に解凍し保存しておきます。

cd /tmp
unzip US-Fixed-with-Satellite-Dec2018.zip
Archive:  US-Fixed-with-Satellite-Dec2018.zip
  inflating: fbd_us_with_satellite_dec2018_v2.csv

psql -U usfixedbb -h localhost -d usfixedbb;
ユーザ usfixedbb のパスワード:
psql (11.7 (Debian 11.7-0+deb10u1))
SSL 接続 (プロトコル: TLSv1.3、暗号化方式: TLS_AES_256_GCM_SHA384、ビット長: 256、圧縮: オフ)
"help" でヘルプを表示します。

DROP TABLE dec2018;
CREATE TABLE dec2018
(
 LogRecNo TEXT NOT NULL,
 Provider_Id TEXT,
 FRN TEXT,
 ProviderName TEXT,
 DBAName TEXT,
 HoldingCompanyName TEXT,
 HocoNum TEXT,
 HocoFinal TEXT,
 StateAbbr TEXT,
 BlockCode TEXT,
 TechCode TEXT,
 Consumer TEXT,
 MaxAdDown TEXT,
 MaxAdUp TEXT,
 Business TEXT,
 MaxCIRDown TEXT,
 MaxCIRUp TEXT,
 PRIMARY KEY (LogRecNo)
); 
usfixedbb=> \q

今回のデータのエンコードは, nkf -gで調べたところ、どういうわけか、SJISでした。（英語以外のプロバイダ名称がありました...さすがアメリカ。）そのため、先にicovで元データをutf-8に変換しておきます。(外字や機種依存文字もあるかもしれないので、変換前のエンコーディングをSJISではなく、CP932でもよいのですが、念のためWINDOWS-31Jにしておきました。)

time iconv -f WINDOWS-31J -t UTF8 fbd_us_with_satellite_dec2018_v2.csv -o fbd_us_with_satellite_dec2018_v2.utf8.csv

real    3m2.465s
user    1m14.204s
sys     0m17.505s

続いて、ダウンロード・解凍・変換したデータをロードします。

$ psql -U usfixedbb -h localhost -d usfixedbb
ユーザ usfixedbb のパスワード:
psql (11.7 (Debian 11.7-0+deb10u1))
SSL 接続 (プロトコル: TLSv1.3、暗号化方式: TLS_AES_256_GCM_SHA384、ビット長: 256、圧縮: オフ)
"help" でヘルプを表示します。

usfixedbb=> \timing
タイミングは on です。
usfixedbb=> copy dec2018 from '/tmp/fbd_us_with_satellite_dec2018_v2.utf8.csv' WITH csv header;
COPY 70788824
時間: 3209194.600 ミリ秒(53:29.195)

なお、今回のマシンは、Hyper-V上のDebian10/Busterで、メモリは8GBを割り当てていますので、7千万件の読み込みとしては、チューニングもしていませんし、まずまずかなと思います。 また、データのロードがすんだら、外部から接続できるようにしておきます。

# /etc/postgresql/11/main/pg_hba.conf に追加
hostssl all             all             192.168.3.0/24          md5
hostssl all             all             10.1.4.0/24             md5
hostssl all             all             10.1.2.0/24             md5
# /etc/postgresql/11/main/postgresql.conf を編集(カンマ区切り。pgがリッスンするアドレスを記入)
listen_addresses = 'localhost,X.X.X.X'
# pgの再起動
/etc/init.d/postgresql restart
# 接続テスト
$ psql postgres://usfixedbb@X.X.X.X:5432/usfixedbb
Password for user usfixedbb:
psql (11.7 (Debian 11.7-0+deb10u1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

以下、総件数のクエリ結果です。

usfixedbb=> select count(*) from dec2018;
  count
----------
 70788824
(1 row)

Time: 324057.473 ms (05:24.057)

3.クエリーについて
前回のクエリーは

select "Max Advertised Downstream Speed (mbps)", "Provider ID", "Provider Name", count(*) from testtable02 group by "Max Advertised Downstream Speed (mbps)", "Provider ID", "Provider Name" order by count(*) desc;

でしたが、今回はフィールド名が異なるので、調整しますと、

select "maxaddown","provider_id","providername", count(*) from dec2018 group by "maxaddown", "provider_id", "providername" order by count(*) desc;

となります。なお実行時間はTime: 275317.793 ms (04:35.318)でした。
4. おまけ
今回のクエリーの結果をODBCなどで取り込むにはクライアントマシンに負荷がかかりすぎるので、クエリの結果をテーブルとして新規作成し、そのテーブルを開くようにします。(いわゆるサマリーテーブルです。)
sqlコマンドは以下のようになります。

CREATE TABLE result_001 AS
select "maxaddown","provider_id","providername", count(*) from dec2018 group by "maxaddown", "provider_id", "providername" order by count(*) desc;

なお、ざっと見たところ、前回とあまり変わりがありませんでしたが、通信衛星を用いているケースが最も多く、通信環境の整っていない地域からの比較的低速なインターネット接続が非常に多数あることはわかりました。いずれにしても、前回同様、米国では高速なブロードバンドが全域で提供されているというわけではなく、一部改善（高速化）の接続が増えているものの、まだまだ改善の余地があるということだと思います。高速な5Gの普及が望まれているのもうなずけるところですね。

今回はここまでです。以上。