TRANSFAC® をSQLで検索できるようにしたい
最も網羅的な転写因子に関するDatabaseのTRANSFAC®だけれど、Download版のライセンスを購入した際に含まれる、EMBL Formatで記載されているAscii flatfile を適切にSQLに登録して活用するのは簡単では無かった
TRANSFAC® 2019.3版からはjson形式のデータが含まれたので、MySQLのDocument Storeに登録して利用してみた
MySQLへのデータインポート
ここでは次の環境を利用している
| System | |
|---|---|
| DB Server | MySQL8.0.18 |
| Client | MySQL Shell |
TRANSFACのデータの展開
TFP_2019.3_data.tar.gzを展開してjsonファイルを確認する
$ mkdir ./TRANSFAC
$ cd ./TRANSFAC
$ tar -zxf TFP_2019.3_data.tar.gz
$ cd ./json
jsonディレクトリに次の5つのjsonファイルが存在する
json/gene.json
json/factor.json.gz
json/site.json
json/factor_wo_fragments.json
json/matrix.json
フラグメント情報を含むjson/factor.json.gzは展開すると8GB近いサイズになる
各jsonオブジェクトのキーと値については、下記のhtmlファイルに記述されているので必要に応じて参照して欲しい
doc/factor_json.html
doc/site_json.html
doc/gene_json.html
doc/matrix_json.html
MySQL shellを利用してデータベースを作成する
MySQL Shellを利用してMySQLサーバーに接続し、\sqlでsqlモードに移行する
＄ mysqlsh --mysqlx -u root --password=${MYSQL_ROOT_PASSWORD} -h localhost -P 33060
Logger: Tried to log to an uninitialized logger.
MySQL Shell 8.0.18
Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
WARNING: Using a password on the command line interface can be insecure.
Creating an X protocol session to 'root@localhost:33060'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 14 (X protocol)
Server version: 8.0.18 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL [localhost+ ssl] JS> \sql
Switching to SQL mode... Commands end with ;
MySQL [localhost+ ssl] SQL>
transfacデータベースを作成
MySQL [localhost+ ssl] SQL> CREATE DATABASE `transfac` ;
Query OK, 1 row affected (0.0055 sec)
transfacデータベースを選択
MySQL [localhost+ ssl] SQL> \use transfac
Default schema set to `transfac`.
Fetching table and column names from `transfac` for auto-completion... Press ^C to stop.
MySQL [localhost+ ssl/transfac] SQL>
JSON ファイルのインポート(失敗例)
MySQL Shell では Pythonがサポートされているのでpythonモードで作業を実施してみる
\pyでpythonモードに移行し、util.import_json()でfactor_wo_fragments.jsonをインポートする
MySQL [localhost+ ssl] JS> \py
Switching to Python mode...
MySQL [localhost+ ssl] Py> \use transfac
Default schema `transfac` accessible through db.
MySQL [localhost+ ssl/transfac] Py> util.import_json('/data/json/factor_wo_fragments.json')
Importing from file "/data/json/factor_wo_fragments.json" to collection `transfac`.`factor_wo_fragments` in MySQL Server at localhost:33060
Processed 0 bytes in 0 documents in 0.0312 sec (0.00 documents/s)
Total successfully imported documents 0 (0.00 documents/s)
Traceback (most recent call last):
File "<string>", line 1, in <module>
SystemError: ArgumentError: Util.import_json: Input does not start with a JSON object at offset 0
SystemError: ArgumentError: Util.import_json: Input does not start with a JSON object at offset 0のエラーで失敗する
ファイルを確認するため、jsonのファイルをロードしてjson objectの個数を確認してみる
直接pythonで記述できるのでとても便利
MySQL [localhost+ ssl/transfac] Py> import json
MySQL [localhost+ ssl/transfac] Py> with open('/data/json/factor_wo_fragments.json', 'r', encoding='utf-8')as fh:factor = json.load(fh)
MySQL [localhost+ ssl/transfac] Py> len(factor)
94129
factorには94129個のエントリーが有る事がわかる
jsonのファイルとしても問題は無い
SystemError: ArgumentError: Util.import_json: Input does not start with a JSON object at offset 0をググる
Oracleのサイトの情報以外には的確なものが引っかからない
ERROR: Util.importJson: Input does not start with a JSON ...
でも、Oracleのこの情報を参照するにはサポート契約が必要で参照できない
そもそも、このエラーJSONのオブジェクトがoffset 0にないとはどういう事なのか？
JSONのarray は object では無いと言いたいのか？
JSONのArrayから要素を抜き出し、単一のJSON objectを作成して util.import_json() を実行してみる
先程取り出したpython のfactor辞書の1要素を取得してjsonのファイルとして出力
MySQL [localhost+ ssl/transfac] Py> with open('/data/json/factor_temp.json', 'w', encoding='utf-8') as fh: json.dump(factor.pop(), fh)
util.import_json()でインポート
MySQL [localhost+ ssl/transfac] Py> util.import_json('/data/json/factor_temp.json')
Importing from file "/data/json/factor_temp.json" to collection `transfac`.`factor_temp` in MySQL Server at localhost:33060
.. 1.. 1
Processed 1.37 KB in 1 document in 0.0068 sec (146.34 documents/s)
Total successfully imported documents 1 (146.34 documents/s)
MySQL [localhost+ ssl/transfac] Py> session.sql('show tables;')
+---------------------+
| Tables_in_transfac |
+---------------------+
| factor_temp |
| factor_wo_fragments |
+---------------------+
2 rows in set (0.0020 sec)
MySQL [localhost+ ssl/transfac] Py> session.sql('select * from factor_temp;')
（省略)
factor_tempが作成され、1要素が正常にインポートされた
JSON objectのインポート(成功例)
上記を踏まえて、次の手順でfactor.jsonファイルをインポートする
- factor_wo_fragments と factor_tempを消去
- JSON object を列記したfactor.jsonファイルを作成（python のワンライナー)
-
util.import_json()でjsonを登録
MySQL [localhost+ ssl/transfac] Py> session.sql('drop table factor_wo_fragments;')
MySQL [localhost+ ssl/transfac] Py> session.sql('drop table factor_temp;')
MySQL [localhost+ ssl/transfac] Py> with open('/data/json/factor.json', 'w', encoding='utf-8') as fh: fh.writelines('\n'.join([json.dumps(item) for item in factor]))
MySQL [localhost+ ssl/transfac] Py> util.import_json('/data/json/factor.json')
Importing from file "/data/json/factor.json" to collection `transfac`.`factor` in MySQL Server at localhost:33060
.. 28278.. 94129.. 94129
Processed 128.52 MB in 94129 documents in 11.9497 sec (7.88K documents/s)
Total successfully imported documents 94129 (7.88K documents/s)
94129要素のJSON objectの登録に成功した
SQLを利用してFactorを検索する
\sqlでSQLモードに移行してカラムを確認する
MySQL [localhost+ ssl/transfac] Py> \sql
MySQL [localhost+ ssl/transfac] SQL> DESCRIBE factor ;
+-------+---------------+------+-----+---------+------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+------------------+
| doc | json | YES | | NULL | |
| _id | varbinary(32) | NO | PRI | NULL | STORED GENERATED |
+-------+---------------+------+-----+---------+------------------+
2 rows in set (0.0023 sec)
JSONのキーでSQL検索
Factor のAccession から3個取得
MySQL [localhost+ ssl/transfac] SQL> SELECT doc->>"$.acc" FROM factor LIMIT 3 ;
+---------------+
| doc->>"$.acc" |
+---------------+
| T00001 |
| T00002 |
| T00003 |
+---------------+
3 rows in set (0.0022 sec)
Factorの中で、生物種がヒトのAccession を5個取得
MySQL [localhost+ ssl/transfac] SQL> SELECT doc->>"$.acc" FROM factor WHERE doc->>'$.organism' like "%human%" LIMIT 5 ;
+---------------+
| doc->>"$.acc" |
+---------------+
| T00001 |
| T00015 |
| T00025 |
| T00029 |
| T00035 |
+---------------+
5 rows in set (0.0011 sec)
このようにして、TRANSFAC® 2019.3のJSONのファイルとMySQL8.0を利用してTRANSFAC®のFactorを自由に検索できるようにする事ができた
今回はここまで