SQLを使用してDrupalのデータをMySQLからCSV出力する方法

Nid: 755

データ件数が数万件を超えるなど大量データを扱う場合、phpを経由するViewsやdrushコマンドは負荷が高く出力が困難なので、APIやSQLコマンドを使用します。

ノード情報をCSV出力するためのSQL文 - 都道府県コンテンツタイプの例

$ sudo mysql -uroot
MariaDB [(none)]> show databases;
MariaDB [(none)]> connect db_tables.octaviadata.com
MariaDB [db_tables.octaviadata.com]> show tables;
-- jp_regionsコンテンツタイプのデータ。
MariaDB [db_tables.octaviadata.com]> select nid,type,title from node where type='jp_regions' limit 3;
+--------+------------+--------------------------------+
| nid    | type       | title                          |
+--------+------------+--------------------------------+
| 219062 | jp_regions | 埼玉県                         |
| 219063 | jp_regions | 高知県                         |
| 219064 | jp_regions | 埼玉県さいたま市南区           |
+--------+------------+--------------------------------+
3 rows in set (0.00 sec)

-- jp_regionsコンテンツタイプのkanaフィールドの値。drupalのフィールドは、データベース上ではテーブルとして格納されている。
MariaDB [db_tables.octaviadata.com]> select entity_type,bundle,entity_id,field_kana_value from field_data_field_kana where bundle='jp_regions' limit 3;
+-------------+------------+-----------+--------------------+
| entity_type | bundle     | entity_id | field_kana_value   |
+-------------+------------+-----------+--------------------+
| node        | jp_regions |    219062 | さいたまけん       |
| node        | jp_regions |    219063 | こうちけん         |
| node        | jp_regions |    219064 | みなみく           |
+-------------+------------+-----------+--------------------+
3 rows in set (0.00 sec)

-- jp_regionsコンテンツタイプのjpregionstypeフィールドの値
MariaDB [db_tables.octaviadata.com]> select entity_type,bundle,entity_id,field_jpregionstype_value from field_data_field_jpregionstype where bundle='jp_regions' limit 5;
+-------------+------------+-----------+---------------------------+
| entity_type | bundle     | entity_id | field_jpregionstype_value |
+-------------+------------+-----------+---------------------------+
| node        | jp_regions |    219062 | pref                      |
| node        | jp_regions |    219063 | pref                      |
| node        | jp_regions |    219064 | ward                      |
| node        | jp_regions |    219065 | city                      |
| node        | jp_regions |    219066 | city                      |
+-------------+------------+-----------+---------------------------+
5 rows in set (0.00 sec)

jp_regionsコンテンツタイプのnodeから、jpregionstype='pref'の都道府県データを抽出し、かなを表示。

MariaDB [db_tables.octaviadata.com]> SELECT node.nid,node.title,field_data_field_kana.field_kana_value 
FROM node JOIN field_data_field_kana ON node.nid = field_data_field_kana.entity_id 
JOIN field_data_field_jpregionstype ON node.nid = field_data_field_jpregionstype.entity_id 
WHERE field_data_field_jpregionstype.field_jpregionstype_value="pref" 
INTO OUTFILE '/tmp/pref.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
$ head /tmp/pref.csv 
"219062","埼玉県","さいたまけん"
"219063","高知県","こうちけん"
"219174","長崎県","ながさきけん"
"219285","岐阜県","ぎふけん"
"219396","香川県","かがわけん"
"219507","岩手県","いわてけん"
"219618","広島県","ひろしまけん"
"219729","栃木県","とちぎけん"
"219840","佐賀県","さがけん"
"219951","山形県","やまがたけん"

SQL作成に必要な各種フィールド情報は、以下のように確認します。

 

コンテンツタイプの一覧

$ sudo drush -y dl drush_typeinfo
$ drush typeinfo-list | grep '^ node'

または以下のSQLを使用。

$ drush sqlq "SELECT type FROM node_type;"
article
country_calling_codes
feed
feed_item
jp_regions

 

コンテンツタイプ jp_regionsのフィールド一覧
・nid
・カナ field_kana
・行政単位区分(都道府県、区、市、群、町、村、) field_jpregionstype

$ drush typeinfo jp_regions
jp-regions全国地方公共団体コード                                                                  [ok]
 Field                           Required  Type               Widget                      Label                         
 body                            No        text_with_summary  text_textarea_with_summary  Body                          
 field_areas                     No        entityreference    er_plus_autocomplete        areas                         
 field_city_id                   No        number_integer     number                      city_id                       
 field_code                      No        text               text_textfield              code                          
 field_district_id               No        entityreference    er_plus_autocomplete        district_id                   
 field_id                        No        text               text_textfield              id                            
 field_jpregionstype             No        list_text          options_select              type                          
 field_kana                      No        text               text_textfield              かな                          
 field_latin                     No        text               text_textfield              latin                         
 field_name                      No        text               text_textfield              name                          
 field_neighbor_region_ids       No        entityreference    er_plus_autocomplete        neighbor_region_ids           
 field_office                    No        field_collection   field_collection_embed      office                        
 field_office_address            No        text               text_textfield              office_address                
 field_office_position           No        geofield           geofield_latlon             office_position               
 field_parent_region_id          No        text               text_textfield              parent_region_id              
 field_pref_id                   No        number_integer     number                      pref_id                       
 field_url                       No        link_field         link_field                  url       

 

MySQL上のDrupalフィールド情報

MariaDB [db_tables.octaviadata.com]> desc node;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| nid       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| vid       | int(10) unsigned | YES  | UNI | NULL    |                |
| type      | varchar(32)      | NO   | MUL |         |                |
| language  | varchar(12)      | NO   | MUL |         |                |
| title     | varchar(255)     | NO   | MUL |         |                |
| uid       | int(11)          | NO   | MUL | 0       |                |
| status    | int(11)          | NO   | MUL | 1       |                |
| created   | int(11)          | NO   | MUL | 0       |                |
| changed   | int(11)          | NO   | MUL | 0       |                |
| comment   | int(11)          | NO   |     | 0       |                |
| promote   | int(11)          | NO   | MUL | 0       |                |
| sticky    | int(11)          | NO   |     | 0       |                |
| tnid      | int(10) unsigned | NO   | MUL | 0       |                |
| translate | int(11)          | NO   | MUL | 0       |                |
| uuid      | char(36)         | NO   | MUL |         |                |
+-----------+------------------+------+-----+---------+----------------+
MariaDB [db_tables.octaviadata.com]> desc field_data_field_kana;
+-------------------+------------------+------+-----+---------+-------+
| Field             | Type             | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+-------+
| entity_type       | varchar(128)     | NO   | PRI |         |       |
| bundle            | varchar(128)     | NO   | MUL |         |       |
| deleted           | tinyint(4)       | NO   | PRI | 0       |       |
| entity_id         | int(10) unsigned | NO   | PRI | NULL    |       |
| revision_id       | int(10) unsigned | YES  | MUL | NULL    |       |
| language          | varchar(32)      | NO   | PRI |         |       |
| delta             | int(10) unsigned | NO   | PRI | NULL    |       |
| field_kana_value  | varchar(255)     | YES  |     | NULL    |       |
| field_kana_format | varchar(255)     | YES  | MUL | NULL    |       |
+-------------------+------------------+------+-----+---------+-------+
MariaDB [db_tables.octaviadata.com]> desc field_data_field_jpregionstype;
+---------------------------+------------------+------+-----+---------+-------+
| Field                     | Type             | Null | Key | Default | Extra |
+---------------------------+------------------+------+-----+---------+-------+
| entity_type               | varchar(128)     | NO   | PRI |         |       |
| bundle                    | varchar(128)     | NO   | MUL |         |       |
| deleted                   | tinyint(4)       | NO   | PRI | 0       |       |
| entity_id                 | int(10) unsigned | NO   | PRI | NULL    |       |
| revision_id               | int(10) unsigned | YES  | MUL | NULL    |       |
| language                  | varchar(32)      | NO   | PRI |         |       |
| delta                     | int(10) unsigned | NO   | PRI | NULL    |       |
| field_jpregionstype_value | varchar(255)     | YES  | MUL | NULL    |       |
+---------------------------+------------------+------+-----+---------+-------+