回答編集履歴
1
CSV変換のサンプルを追記
test
CHANGED
|
@@ -17,3 +17,85 @@
|
|
|
17
17
|
|
|
18
18
|
> 2016-06-13T17:43:50.100400
|
|
19
19
|
|
|
20
|
+
|
|
21
|
+
## 追記 (CSVの変換例)
|
|
22
|
+
|
|
23
|
+
InfluxDBのV1 APIとPython Scriptを使った場合の例を追記しておきます（あくまで簡易的なサンプルです）。なお、V2 APIの場合には、ISO形式で`_time`属性が出力されるようですし、telegrafのプラグインで変換するアプローチもあるようです。
|
|
24
|
+
- [community.influxdata - Reformatting the CSV Data](https://community.influxdata.com/t/reformatting-the-csv-data/25279)
|
|
25
|
+
- [community.influxdata - Newbie Questions: getting data into InfluxDB easily/ timestamp format](https://community.influxdata.com/t/newbie-questions-getting-data-into-influxdb-easily-timestamp-format/19369)
|
|
26
|
+
|
|
27
|
+
### InfluxDBへのダミーデータ登録
|
|
28
|
+
|
|
29
|
+
以下のコマンドを２回実行。
|
|
30
|
+
|
|
31
|
+
```
|
|
32
|
+
$ curl --request POST \
|
|
33
|
+
"http://localhost:8086/api/v2/write?org=sample-org-name&bucket=sample-bucket-name&precision=ns" \
|
|
34
|
+
--header "Authorization: Token $TOKEN" \
|
|
35
|
+
--header "Content-Type: text/plain; charset=utf-8" \
|
|
36
|
+
--header "Accept: application/json" \
|
|
37
|
+
--data-binary '
|
|
38
|
+
airSensors,sensor_id=TLM0201 temperature=73.97038159354763,humidity=35.23103248356096,co=0.48445310567793615
|
|
39
|
+
airSensors,sensor_id=TLM0202 temperature=75.30007505999716,humidity=35.651929918691714,co=0.5141876544505826
|
|
40
|
+
'
|
|
41
|
+
```
|
|
42
|
+
|
|
43
|
+
### CSV形式でのエクスポート
|
|
44
|
+
|
|
45
|
+
```
|
|
46
|
+
$ curl 'http://127.0.0.1:8086/query?db=sample-bucket-1' \
|
|
47
|
+
--header "Authorization: Token $TOKEN" \
|
|
48
|
+
--header 'Accept: application/csv' \
|
|
49
|
+
--data-urlencode 'q=SELECT * FROM airSensors' > input.csv
|
|
50
|
+
```
|
|
51
|
+
|
|
52
|
+
```
|
|
53
|
+
$ cat input.csv
|
|
54
|
+
name,tags,time,co,humidity,sensor_id,temperature
|
|
55
|
+
airSensors,,1672645590018142521,0.48445310567793615,35.23103248356096,TLM0201,73.97038159354763
|
|
56
|
+
airSensors,,1672645590018142521,0.5141876544505826,35.651929918691714,TLM0202,75.30007505999716
|
|
57
|
+
airSensors,,1672645597434047671,0.48445310567793615,35.23103248356096,TLM0201,73.97038159354763
|
|
58
|
+
airSensors,,1672645597434047671,0.5141876544505826,35.651929918691714,TLM0202,75.30007505999716
|
|
59
|
+
```
|
|
60
|
+
|
|
61
|
+
### カラム値の変換
|
|
62
|
+
|
|
63
|
+
timeカラムの情報からtime_in_iso_formatカラムを作成（UTC時間で出力されるため、日本時間とは9時間ズレます）。
|
|
64
|
+
|
|
65
|
+
```
|
|
66
|
+
$ python convert.py input.csv output.csv
|
|
67
|
+
```
|
|
68
|
+
|
|
69
|
+
```
|
|
70
|
+
$ cat output.csv
|
|
71
|
+
name,tags,time,co,humidity,sensor_id,temperature,time_in_iso_format
|
|
72
|
+
airSensors,,1672645590018142521,0.48445310567793615,35.23103248356096,TLM0201,73.97038159354763,2023-01-02T07:46:30.018142
|
|
73
|
+
airSensors,,1672645590018142521,0.5141876544505826,35.651929918691714,TLM0202,75.30007505999716,2023-01-02T07:46:30.018142
|
|
74
|
+
airSensors,,1672645597434047671,0.48445310567793615,35.23103248356096,TLM0201,73.97038159354763,2023-01-02T07:46:37.434048
|
|
75
|
+
airSensors,,1672645597434047671,0.5141876544505826,35.651929918691714,TLM0202,75.30007505999716,2023-01-02T07:46:37.434048
|
|
76
|
+
```
|
|
77
|
+
|
|
78
|
+
### スクリプト（あくまで簡易的な例です）
|
|
79
|
+
|
|
80
|
+
```python
|
|
81
|
+
$ cat convert.py
|
|
82
|
+
import csv
|
|
83
|
+
from datetime import datetime
|
|
84
|
+
import sys
|
|
85
|
+
|
|
86
|
+
input_file = open(sys.argv[1], newline='')
|
|
87
|
+
output_file = open(sys.argv[2], 'w', newline='')
|
|
88
|
+
|
|
89
|
+
reader = csv.DictReader(input_file)
|
|
90
|
+
new_fieldnames = reader.fieldnames + ['time_in_iso_format']
|
|
91
|
+
|
|
92
|
+
writer = csv.DictWriter(output_file, fieldnames=new_fieldnames)
|
|
93
|
+
writer.writeheader()
|
|
94
|
+
|
|
95
|
+
for row in reader:
|
|
96
|
+
time_in_nano_second = int(row['time'])
|
|
97
|
+
time_in_second = time_in_nano_second / (1000 * 1000 * 1000)
|
|
98
|
+
row['time_in_iso_format'] = datetime.utcfromtimestamp(time_in_second).isoformat()
|
|
99
|
+
|
|
100
|
+
writer.writerow(row)
|
|
101
|
+
```
|