search

masayuki14 / worklog

Record working log by issues.
MIT License
0 stars 0 forks source link

resas-api etc #16

Closed masayuki14 closed 5 years ago

masayuki14 commented 5 years ago

API 概要 https://opendata.resas-portal.go.jp/docs/api/v1/index.html

masayuki14 commented 5 years ago

登録が必要　https://opendata.resas-portal.go.jp/form.html

masayuki14 commented 5 years ago

メール届いた。 URLひらいたらAPIキーが表示された。これを逃したらもう知る方法はなさそうだ。

masayuki14 commented 5 years ago 
$ curl -H 'X-API-KEY: api_key' -X GET 'https://opendata.resas-portal.go.jp/api/v1/cities?prefCode=1'

で取得できた。

masayuki14 commented 5 years ago 
shell.connect('mysqlx://username@localhost');
schema = session.getSchema('username')
schema.createCollection('cities')
collection = schema.getCollection('cities')

できたテーブル

CREATE TABLE `cities` (
  `doc` json DEFAULT NULL,
  `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
masayuki14 commented 5 years ago

↑ mysqlsh での操作。

masayuki14 commented 5 years ago 
JS>  collection.add({type:"sample"})
Query OK, 1 item affected (0.0863 sec)

mysql> select * From cities;
+-----------------------------------------------------------+------------------------------+
| doc                                                       | _id                          |
+-----------------------------------------------------------+------------------------------+
| {"_id": "00005bb44ded0000000000000001", "type": "sample"} | 00005bb44ded0000000000000001 |
+-----------------------------------------------------------+------------------------------+
1 row in set (0.00 sec)
insert into cities (`doc`) values ('{"type":"sample","_id":"00000009"}')

INSERT の時はJSONに _id キーがないといけない。

masayuki14 commented 5 years ago 
CREATE TABLE `cities2` (
  `doc` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
masayuki14 commented 5 years ago

shell からJSONそのまま入れたい。

$ mysql -u username -p  database < \
<(echo "insert into cities2 (\`doc\`) values ('" $(curl -H 'X-API-KEY: api_key' -X GET 'https://opendata.resas-portal.go.jp/api/v1/cities?prefCode=17')  "')" )

17は石川県だった。

masayuki14 commented 5 years ago

はいった！

mysql> select * From cities2 \G
*************************** 1. row ***************************
doc: {"result": [{"cityCode": "17201", "cityName": "金沢市", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17202", "cityName": "七尾市", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17203", "cityName": "小松市", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17204", "cityName": "輪島市", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17205", "cityName": "珠洲市", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17206", "cityName": "加賀市", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17207", "cityName": "羽咋市", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17209", "cityName": "かほく市", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17210", "cityName": "白山市", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17211", "cityName": "能美市", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17212", "cityName": "野々市市", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17324", "cityName": "川北町", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17361", "cityName": "津幡町", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17365", "cityName": "内灘町", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17384", "cityName": "志賀町", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17386", "cityName": "宝達志水 町", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17407", "cityName": "中能登町", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17461", "cityName": "穴水町", "prefCode": 17, "bigCityFlag": "0"}, {"cityCode": "17463", "cityName": "能登町", "prefCode": 17, "bigCityFlag": "0"}], "message": null}
1 row in set (0.00 sec)
masayuki14 commented 5 years ago 
select id, trans.*
from block, JSON_TABLE(
  `transaction`,
'$[*]'
  columns (
    name varchar(32) path '$.name',
    `date` date path '$.date',
    report varchar(128) path '$.report'
)
) trans;
masayuki14 commented 5 years ago

を、参考に。

select city.*
from cities2, JSON_TABLE(
  `doc`,
  "$.result[*]"
  columns (
     prefCode int path '$.prefCode',
     cityCode varchar(64) path '$.cityCode',
     cityName varchar(64) path '$.cityName',
     bigCityFlag varchar(4) path '$.bigCityFlag'
  )
) city

できるはず。

masayuki14 commented 5 years ago

できない。 SELECT command denied to user 'username'@'localhost' for table 'json_table'

権限がないっぽい。 root ないける。

masayuki14 commented 5 years ago

https://bugs.mysql.com/bug.php?id=90610 フォーラムにも投稿があったお。

masayuki14 commented 5 years ago

root で権限をつけてみる。

mysql> grant select on *.* to 'planna'@'localhost';
Query OK, 0 rows affected (0.05 sec)
mysql> show grants;
+------------------------------------------------------------+
| Grants for planna@localhost                                |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO `planna`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `planna`.* TO `planna`@`localhost` |
+------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants;
+------------------------------------------------------------+
| Grants for planna@localhost                                |
+------------------------------------------------------------+
| GRANT SELECT ON *.* TO `planna`@`localhost`                |
| GRANT ALL PRIVILEGES ON `planna`.* TO `planna`@`localhost` |
+------------------------------------------------------------+
2 rows in set (0.00 sec)
masayuki14 commented 5 years ago

だめだ。

masayuki14 commented 5 years ago

まぁ、いいか。root で。

mysql> \e
    -> ;
+----------+----------+-----------------+-------------+
| prefCode | cityCode | cityName        | bigCityFlag |
+----------+----------+-----------------+-------------+
|       17 | 17201    | 金沢市          | 0           |
|       17 | 17202    | 七尾市          | 0           |
|       17 | 17203    | 小松市          | 0           |
|       17 | 17204    | 輪島市          | 0           |
|       17 | 17205    | 珠洲市          | 0           |
|       17 | 17206    | 加賀市          | 0           |
|       17 | 17207    | 羽咋市          | 0           |
|       17 | 17209    | かほく市        | 0           |
|       17 | 17210    | 白山市          | 0           |
|       17 | 17211    | 能美市          | 0           |
|       17 | 17212    | 野々市市        | 0           |
|       17 | 17324    | 川北町          | 0           |
|       17 | 17361    | 津幡町          | 0           |
|       17 | 17365    | 内灘町          | 0           |
|       17 | 17384    | 志賀町          | 0           |
|       17 | 17386    | 宝達志水町      | 0           |
|       17 | 17407    | 中能登町        | 0           |
|       17 | 17461    | 穴水町          | 0           |
|       17 | 17463    | 能登町          | 0           |
+----------+----------+-----------------+-------------+
19 rows in set (0.00 sec)
masayuki14 commented 5 years ago

全部取りに行こう。

masayuki14 commented 5 years ago

都道府県と市町村をそれぞれとって、 JSON_TABLE()でViewつくるかTableにInsertしてJOINすれば 一覧表ができるぜ！ これは shell + mysql だけでできるからかなりかんたんじゃーん。

masayuki14 commented 5 years ago 
while read code
do
  echo $code

  mysql -u username -ppassword  database < \
        <(echo "insert into cities2 (\`doc\`) values ('" \
        $(curl -H 'X-API-KEY: api_key' -X GET 'https://opendata.resas-portal.go.jp/api/v1/cities?prefCode='$code) \
        "')" )
done < <(seq 1 47)

で全部入る。

masayuki14 commented 5 years ago

それぞれのテーブルを用意する。

CREATE TABLE `cities` (
  `prefCode` int(11) DEFAULT NULL,
  `cityCode` varchar(64) NOT NULL,
  `cityName` varchar(64) DEFAULT NULL,
  `bigCityFlag` varchar(4) DEFAULT NULL,
  PRIMARY KEY (`cityCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `prefectures` (
  `prefCode` int(11) NOT NULL,
  `prefName` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`prefCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
masayuki14 commented 5 years ago

INSERT する

insert into prefectures (
  select prefecture.*
  from json_prefecture, JSON_TABLE(
    `doc`,
    "$.result[*]"
    columns (
      prefCode int path '$.prefCode',
      prefName varchar(64) path '$.prefName'
    )
  ) prefecture
);

insert into cities (
  select jcities.*
  from json_cities, JSON_TABLE(
    `doc`,
    "$.result[*]"
    columns (
      prefCode int path '$.prefCode',
      cityCode varchar(64) path '$.cityCode',
      cityName varchar(64) path '$.cityName',
      bigCityFlag varchar(4) path '$.bigCityFlag'
    )
  ) jcities
);

ぜんぶはいったー。

masayuki14 commented 5 years ago

CSVで出力だ。

masayuki14 commented 5 years ago 
mysql> select * from prefectures join cities using (prefCode)  
    into outfile '/tmp/prefecture.csv' 
    fields terminated by ',' ;

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

これこの前言ってたやつだなー。と思って確認。

mysql> show variables like 'secure%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec
masayuki14 commented 5 years ago 
mysql> select * from prefectures join cities using (prefCode)
     into outfile '/var/lib/mysql-files/prefecture.csv'
     fields terminated by ',' ;
Query OK, 1920 rows affected (0.00 s

done!