search

pgspider / influxdb_fdw

InfluxDB Foreign Data Wrapper for PostgreSQL.
Other
58 stars 14 forks source link

docs: how to get the last sample records in one step #44

Open cobolbaby opened 11 months ago

cobolbaby commented 11 months ago

How to quickly retrieve the latest sampling records? Currently, my approach is to use a query similar to the following:

select max(time) from dm.iot_sqt_agv_count where time > now() - interval '1 hour';
select * from dm.iot_sqt_agv_count where time = v_max_time;

How to use the last function in the README?

MinhLA1410 commented 6 months ago

Hi @cobolbaby ,

How to use the last function in the README?

Last function has 3 ways to use follow https://docs.influxdata.com/influxdb/v1/query_language/functions/#last. We also provide 3 this ways on influxdb_fdw:

cobolbaby commented 6 months ago

The default UDFs might not meet the requirements; they either fail to retrieve results or return text data that looks like tuples. Could you give me some suggestions?

bdc=# \d dm.iot_sqt_agv_count;
                        Foreign table "dm.iot_sqt_agv_count"
  Column  |           Type           | Collation | Nullable | Default | FDW options 
----------+--------------------------+-----------+----------+---------+-------------
 time     | timestamp with time zone |           |          |         | 
 agvState | character varying        |           |          |         | 
 value    | numeric                  |           |          |         | 
Server: influxdb_iot
FDW options: ("table" 'F3AGV.count', tags 'agvState,value', schemaless 'true')

bdc=# select * from dm.iot_sqt_agv_count order by time desc limit 10;
            time            |  agvState  | value 
----------------------------+------------+-------
 2024-05-20 18:15:55.562+08 | onlineAGV  |    88
 2024-05-20 18:15:55.562+08 | LOCKED     |    11
 2024-05-20 18:15:55.562+08 | IDLE       |     5
 2024-05-20 18:15:55.562+08 | offlineAGV |     3
 2024-05-20 18:15:55.562+08 | BUSY       |    70
 2024-05-20 18:15:55.562+08 | totalAGV   |    91
 2024-05-20 18:15:55.562+08 | CHARGING   |     1
 2024-05-20 18:15:55.562+08 | ERROR      |     1
 2024-05-20 18:14:55.572+08 | totalAGV   |    91
 2024-05-20 18:14:55.572+08 | BUSY       |    71
(10 rows)

bdc=# select last(time, "agvState") from dm.iot_sqt_agv_count;
 last 
------
(0 rows)

bdc=# select last(time, value) from dm.iot_sqt_agv_count;
ERROR:  stub last_sfunc(anyelement, timestamp with time zone, anyelement) is called
CONTEXT:  PL/pgSQL function last_sfunc(anyelement,timestamp with time zone,anyelement) line 3 at RAISE

bdc=# select last_all(*) from dm.iot_sqt_agv_count;
                      last_all                       
-----------------------------------------------------
 (2024-05-20T10:16:55.548Z,,"{\"value\" : \"91\" }")
(1 row)

bdc=# select last('/value/') from dm.iot_sqt_agv_count;
                        last                         
-----------------------------------------------------
 (2024-05-20T10:16:55.548Z,,"{\"value\" : \"91\" }")
(1 row)

bdc=# select max(time) from dm.iot_sqt_agv_count where time > now() - interval '1 hour';
            max             
----------------------------
 2024-05-20 18:17:55.564+08
(1 row)

bdc=# select * from dm.iot_sqt_agv_count where time = '2024-05-20 18:17:55.564+08';
            time            |  agvState  | value 
----------------------------+------------+-------
 2024-05-20 18:17:55.564+08 | BUSY       |    70
 2024-05-20 18:17:55.564+08 | CHARGING   |     1
 2024-05-20 18:17:55.564+08 | ERROR      |     1
 2024-05-20 18:17:55.564+08 | IDLE       |     7
 2024-05-20 18:17:55.564+08 | LOCKED     |    10
 2024-05-20 18:17:55.564+08 | offlineAGV |     2
 2024-05-20 18:17:55.564+08 | onlineAGV  |    89
 2024-05-20 18:17:55.564+08 | totalAGV   |    91
(8 rows)
MinhLA1410 commented 3 months ago

Hello @cobolbaby Sorry for late response.

Could you give me some suggestions?

The last() function cannot work because the last() function only works for key fields. https://docs.influxdata.com/influxdb/v1/query_language/functions/#last As your FDW options FDW options: ("table" 'F3AGV.count', tags 'agvState,value', schemaless 'true') avgState and value column is the tag fields, so the error occurs.

cobolbaby commented 3 months ago

How to convert the text (2024-05-20T10:16:55.548Z,,"{\"value\" : \"91\" }") like tuples to columns?

MinhLA1410 commented 3 months ago

How to convert the text (2024-05-20T10:16:55.548Z,,"{\"value\" : \"91\" }") like tuples to columns?

Can you try select (last_all(*)::dm.iot_sqt_agv_count).* from dm.iot_sqt_agv_count ?

cobolbaby commented 3 months ago

select (last_all()::dm.iot_sqt_agv_count). from dm.iot_sqt_agv_count

ERROR: invalid input syntax for type numeric: "{"value" : "91" }"

SQL state: 22P02

MinhLA1410 commented 3 months ago

ERROR: invalid input syntax for type numeric: "{"value" : "91" }"

Thank you for feedback. But I would like to know your table schema is correct?

bdc=# \d dm.iot_sqt_agv_count;
                        Foreign table "dm.iot_sqt_agv_count"
  Column  |           Type           | Collation | Nullable | Default | FDW options 
----------+--------------------------+-----------+----------+---------+-------------
 time     | timestamp with time zone |           |          |         | 
 agvState | character varying        |           |          |         | 
 value    | numeric                  |           |          |         | 
Server: influxdb_iot
FDW options: ("table" 'F3AGV.count', tags 'agvState,value', schemaless 'true')

The schemaless mode is enabled. But your column is agvState and value. I don't understand why because Columns of foreign table in schemaless mode are fixed with names and types as below: (time timestamp with time zone, tags jsonb, fields jsonb)

cobolbaby commented 3 months ago

The schemaless mode is enabled. But your column is agvState and value.

You're right, I didn't notice the option. It seems there were no errors with schemaless 'true' before. I'll alter the option and test it again.

ALTER FOREIGN TABLE IF EXISTS dm.iot_sqt_agv_count
    OPTIONS (SET schemaless 'false');

select (last_all(*)::dm.iot_sqt_agv_count).* from dm.iot_sqt_agv_count;

ERROR:  Too many columns.malformed record literal: "(2024-08-05T04:38:22.974Z,,,)" 

ERROR:  malformed record literal: "(2024-08-05T04:38:22.974Z,,,)"
SQL state: 22P02
Detail: Too many columns.

select * from dm.iot_sqt_agv_count order by time desc limit 10;

"2024-08-05 12:46:23.022+08"    "onlineAGV" 88
"2024-08-05 12:46:23.022+08"    "LOCKED"    12
"2024-08-05 12:46:23.022+08"    "IDLE"  18
"2024-08-05 12:46:23.022+08"    "offlineAGV"    3
"2024-08-05 12:46:23.022+08"    "BUSY"  52
"2024-08-05 12:46:23.022+08"    "totalAGV"  91
"2024-08-05 12:46:23.022+08"    "CHARGING"  6
"2024-08-05 12:46:23.022+08"    "ERROR" 0
"2024-08-05 12:45:22.997+08"    "totalAGV"  91
"2024-08-05 12:45:22.997+08"    "BUSY"  50
MinhLA1410 commented 3 months ago

"(2024-08-05T04:38:22.974Z,,,)"

It looks like your remote table (table on Influxdb) has more columns than 3 (time, avgState, value). Because the last_all(*) function is pushed down to influxdb to get the results directly from influxdb. The returned result includes the actual column number but is cast according to the number columns of foreign table so the Too many columns.malformed record literal error is thrown.

It looks like your remote table (table on Influxdb) has more columns than 3 (time, avgState, value).

is it correct?

cobolbaby commented 3 months ago

It looks like your remote table (table on Influxdb) has more columns than 3 (time, avgState, value).

Yes.

MinhLA1410 commented 3 months ago

@cobolbaby

Yes

How to convert the text (2024-05-20T10:16:55.548Z,,"{\"value\" : \"91\" }") like tuples to columns?

To expose it, it must be able to cast the record to a table data type where the data type matches the value (including number of columns, and data type of each column). There are 2 ways: