Re: Re: New user of IoTDB - a few queries

Jialin Qiao Mon, 08 Mar 2021 06:02:27 -0800

Hi Dhruv,

Welcome! Here is some design doc[1] about the query process :)


[1] http://iotdb.apache.org/SystemDesign/DataQuery/DataQuery.html

Thanks,
--
Jialin Qiao
School of Software, Tsinghua University

乔嘉林
清华大学 软件学院

&gt; -----原始邮件-----
&gt; 发件人: "Dhruv Garg" <[email protected]>
&gt; 发送时间: 2021-03-08 21:51:21 (星期一)
&gt; 收件人: [email protected]
&gt; 抄送: 
&gt; 主题: Re: New user of IoTDB - a few queries
&gt; 
&gt; Yep, thanks! I would like to. Just need to find the time. :P
&gt; 
&gt; Regards,
&gt; dgargcs
&gt; 
&gt; On Mon, 8 Mar 2021 at 17:40, Xiangdong Huang <[email protected]> wrote:
&gt; 
&gt; &gt; By the way, welcome to contribute left, right, inner join to IoTDB if 
you
&gt; &gt; like ~ :D
&gt; &gt;
&gt; &gt; -----------------------------------
&gt; &gt; Xiangdong Huang
&gt; &gt; School of Software, Tsinghua University
&gt; &gt;
&gt; &gt;  黄向东
&gt; &gt; 清华大学 软件学院
&gt; &gt;
&gt; &gt;
&gt; &gt; Xiangdong Huang <[email protected]> 于2021年3月8日周一 下午8:09写道：
&gt; &gt;
&gt; &gt; &gt; Hi, yes, root is like "/" in Unix-like OS.
&gt; &gt; &gt;
&gt; &gt; &gt; -----------------------------------
&gt; &gt; &gt; Xiangdong Huang
&gt; &gt; &gt; School of Software, Tsinghua University
&gt; &gt; &gt;
&gt; &gt; &gt;  黄向东
&gt; &gt; &gt; 清华大学 软件学院
&gt; &gt; &gt;
&gt; &gt; &gt;
&gt; &gt; &gt; Dhruv Garg <[email protected]> 于2021年3月8日周一 下午6:14写道：
&gt; &gt; &gt;
&gt; &gt; &gt;&gt; Hello Xiangdong,
&gt; &gt; &gt;&gt;
&gt; &gt; &gt;&gt; Thanks for confirming! One last question for now, all 
storage groups
&gt; &gt; must
&gt; &gt; &gt;&gt; start with "root" right? I cannot have storage groups like 
root1.demo,
&gt; &gt; &gt;&gt; correct?
&gt; &gt; &gt;&gt;
&gt; &gt; &gt;&gt; Regards,
&gt; &gt; &gt;&gt; dgargcs
&gt; &gt; &gt;&gt;
&gt; &gt; &gt;&gt; On Mon, 8 Mar 2021 at 15:37, Xiangdong Huang 
<[email protected]>
&gt; &gt; wrote:
&gt; &gt; &gt;&gt;
&gt; &gt; &gt;&gt; &gt; Hi Dhruv,
&gt; &gt; &gt;&gt; &gt;
&gt; &gt; &gt;&gt; &gt; Yes you are right.  and IoTDB also supports join on 
timestamp across
&gt; &gt; &gt;&gt; &gt; storage groups.
&gt; &gt; &gt;&gt; &gt;
&gt; &gt; &gt;&gt; &gt; Q2: IoTDB does not support that now.
&gt; &gt; &gt;&gt; &gt; Current join looks like outer-join in RDBMS.
&gt; &gt; &gt;&gt; &gt; Supporting left join, right join and inner-join is not 
hard to
&gt; &gt; &gt;&gt; implement,
&gt; &gt; &gt;&gt; &gt; just omit the rows that contain NULL.
&gt; &gt; &gt;&gt; &gt; (Better implementation will need CBO cost-based 
optimization, but
&gt; &gt; IoTDB
&gt; &gt; &gt;&gt; &gt; lacks the statistics info for that.)
&gt; &gt; &gt;&gt; &gt;
&gt; &gt; &gt;&gt; &gt; Best,
&gt; &gt; &gt;&gt; &gt; -----------------------------------
&gt; &gt; &gt;&gt; &gt; Xiangdong Huang
&gt; &gt; &gt;&gt; &gt; School of Software, Tsinghua University
&gt; &gt; &gt;&gt; &gt;
&gt; &gt; &gt;&gt; &gt;  黄向东
&gt; &gt; &gt;&gt; &gt; 清华大学 软件学院
&gt; &gt; &gt;&gt; &gt;
&gt; &gt; &gt;&gt; &gt;
&gt; &gt; &gt;&gt; &gt; Dhruv Garg <[email protected]> 于2021年3月8日周一 下午5:56写道：
&gt; &gt; &gt;&gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; Hello Xiangdong,
&gt; &gt; &gt;&gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; Yes. Let me elaborate a bit about the join 
requirement. Suppose I
&gt; &gt; &gt;&gt; want to
&gt; &gt; &gt;&gt; &gt; &gt; do a join to print out values in root.demo.d1.m1 
and root.demo.d2.m2
&gt; &gt; &gt;&gt; for
&gt; &gt; &gt;&gt; &gt; a
&gt; &gt; &gt;&gt; &gt; &gt; common time range say 2019-12-11 00:00:00 to 
2020-12-18 23:59:59. It
&gt; &gt; &gt;&gt; &gt; should
&gt; &gt; &gt;&gt; &gt; &gt; print out the last two rows from your table.
&gt; &gt; &gt;&gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; In SQL, when we do a join, there are two different 
tables and then
&gt; &gt; we
&gt; &gt; &gt;&gt; do
&gt; &gt; &gt;&gt; &gt; a
&gt; &gt; &gt;&gt; &gt; &gt; join based on some common column =&gt; 
root.demo.d1 is one table (has
&gt; &gt; an
&gt; &gt; &gt;&gt; &gt; "m1"
&gt; &gt; &gt;&gt; &gt; &gt; column) and another table root.demo.d2 (has an 
"m2" column). If I
&gt; &gt; do a
&gt; &gt; &gt;&gt; &gt; join
&gt; &gt; &gt;&gt; &gt; &gt; on d1 and d2 based on timestamp, it will return 
some rows containing
&gt; &gt; &gt;&gt; m1
&gt; &gt; &gt;&gt; &gt; and
&gt; &gt; &gt;&gt; &gt; &gt; m2 where timestamps are the same.
&gt; &gt; &gt;&gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; I think the way to look at join queries in IoTDB 
is slightly
&gt; &gt; &gt;&gt; different.
&gt; &gt; &gt;&gt; &gt; &gt; Here, we have a storage group (root.demo) with 
different time-series
&gt; &gt; &gt;&gt; &gt; (d1.m1
&gt; &gt; &gt;&gt; &gt; &gt; and d2.m2). For each insert in a storage group, I 
think IoTDB
&gt; &gt; &gt;&gt; &gt; automatically
&gt; &gt; &gt;&gt; &gt; &gt; joins these time-series on timestamp since it 
assumes null values
&gt; &gt; for
&gt; &gt; &gt;&gt; &gt; other
&gt; &gt; &gt;&gt; &gt; &gt; time-series that were not given a value during 
insert.  For example,
&gt; &gt; &gt;&gt; if I
&gt; &gt; &gt;&gt; &gt; &gt; do an "insert root.demo.d2(timestamp,m2) values
&gt; &gt; (2021-08-03T00:00:00,
&gt; &gt; &gt;&gt; &gt; &gt; 2021)", it will also append NULL value for 
root.demo.d1.m1 for this
&gt; &gt; &gt;&gt; &gt; &gt; timestamp.
&gt; &gt; &gt;&gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; Thus, to do a join within a storage group for *any 
number* of
&gt; &gt; &gt;&gt; time-series
&gt; &gt; &gt;&gt; &gt; &gt; is straightforward. I think this is trivially done 
by just giving
&gt; &gt; the
&gt; &gt; &gt;&gt; &gt; time
&gt; &gt; &gt;&gt; &gt; &gt; range and time-series names =&gt; "select d1.m1, 
d2.m2 from root.demo
&gt; &gt; &gt;&gt; where
&gt; &gt; &gt;&gt; &gt; &gt; timestamp &gt;= 2019-19-11T00:00:00".
&gt; &gt; &gt;&gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; Next, I thought about a similar query across 
storage groups, say
&gt; &gt; &gt;&gt; &gt; root.demo1
&gt; &gt; &gt;&gt; &gt; &gt; and root.demo2. Here as well, I can run a similar 
query, but only
&gt; &gt; &gt;&gt; start
&gt; &gt; &gt;&gt; &gt; &gt; from a higher level in the storage hierarchy, i.e. 
"root" alone.
&gt; &gt; &gt;&gt; "select
&gt; &gt; &gt;&gt; &gt; &gt; demo1.m1, demo2.m2 from root where timestamp &gt;=
&gt; &gt; 2019-19-11T00:00:00".
&gt; &gt; &gt;&gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; Q1. Is my interpretation of join on timestamp 
correct for IoTDB?
&gt; &gt; &gt;&gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; Q2. Is there a way to omit rows that return one or 
more null column
&gt; &gt; &gt;&gt; &gt; values
&gt; &gt; &gt;&gt; &gt; &gt; in the result?
&gt; &gt; &gt;&gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; PS: Sorry for the long email. I am exploring IoTDB 
for a research
&gt; &gt; &gt;&gt; project
&gt; &gt; &gt;&gt; &gt; &gt; and just want to be clear in my understanding.
&gt; &gt; &gt;&gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; Thanks in advance!
&gt; &gt; &gt;&gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; Regards,
&gt; &gt; &gt;&gt; &gt; &gt; dgargcs
&gt; &gt; &gt;&gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; On Mon, 8 Mar 2021 at 14:12, Xiangdong Huang 
<[email protected]>
&gt; &gt; &gt;&gt; wrote:
&gt; &gt; &gt;&gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; Hi Dhruv,
&gt; &gt; &gt;&gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; 1. Could you give me an example to describe 
what you want to
&gt; &gt; query?
&gt; &gt; &gt;&gt; &gt; &gt; &gt; e.g., there are two time series:
&gt; &gt; &gt;&gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; 
+-----------------------------+---------------+---------------+
&gt; &gt; &gt;&gt; &gt; &gt; &gt; |                         
Time|root.demo.d1.m1|root.demo.d2.m2|
&gt; &gt; &gt;&gt; &gt; &gt; &gt; 
+-----------------------------+---------------+---------------+
&gt; &gt; &gt;&gt; &gt; &gt; &gt; |1970-01-01T08:00:00.001+08:00|            
1.0|            2.0|
&gt; &gt; &gt;&gt; &gt; &gt; &gt; |2019-12-11T10:00:00.000+08:00|            
1.0|            NULL|
&gt; &gt; &gt;&gt; &gt; &gt; &gt; |2020-12-18T11:45:14.519+08:00|            
2.0|            2.0|
&gt; &gt; &gt;&gt; &gt; &gt; &gt; 
+-----------------------------+---------------+---------------+
&gt; &gt; &gt;&gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; or,
&gt; &gt; &gt;&gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; What you want to query? (you can consider it 
as a table in MySQL,
&gt; &gt; &gt;&gt; and
&gt; &gt; &gt;&gt; &gt; &gt; just
&gt; &gt; &gt;&gt; &gt; &gt; &gt; write the sqls you need in mysql.)
&gt; &gt; &gt;&gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; 2. Ah, we are testing the performance of 
v0.12. maybe 2-3 weeks, I
&gt; &gt; &gt;&gt; &gt; think.
&gt; &gt; &gt;&gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; Best,
&gt; &gt; &gt;&gt; &gt; &gt; &gt; -----------------------------------
&gt; &gt; &gt;&gt; &gt; &gt; &gt; Xiangdong Huang
&gt; &gt; &gt;&gt; &gt; &gt; &gt; School of Software, Tsinghua University
&gt; &gt; &gt;&gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt;  黄向东
&gt; &gt; &gt;&gt; &gt; &gt; &gt; 清华大学 软件学院
&gt; &gt; &gt;&gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; Dhruv Garg <[email protected]> 于2021年3月8日周一 
下午4:11写道：
&gt; &gt; &gt;&gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; Hello Xiangdong,
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; Thanks for your response.
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; 1. I have the same use-case: I want to 
run a join query on two
&gt; &gt; &gt;&gt; time
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; series but I think I am getting the 
syntax wrong.
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; I tried a few SQL-like join queries on 
IoTDB v0.11.2 and got the
&gt; &gt; &gt;&gt; &gt; &gt; &gt; following
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; error:  "mismatched input 'JOIN' 
expecting {<eof>, WHERE, ORDER,
&gt; &gt; &gt;&gt; &gt; &gt; SLIMIT,
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; LIMIT, OFFSET, SOFFSET, FILL, GROUP, 
DISABLE, ALIGN, ',', '.'}"
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; Could you please refer me to some 
documentation or provide a
&gt; &gt; &gt;&gt; simple
&gt; &gt; &gt;&gt; &gt; &gt; &gt; example
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; join query for IoTDB?
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; 2. I am also excited to try out the 
cluster version. Is there an
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; approximate timeline for v0.12 rollout?
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; Thanks in advance.
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; Regards,
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; dgargcs
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; On Tue, 2 Mar 2021 at 17:24, Xiangdong 
Huang &lt;
&gt; &gt; [email protected]&gt;
&gt; &gt; &gt;&gt; &gt; &gt; wrote:
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; Hi Dhruv,
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; Welcome!
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; 1. "join" for what requirements?
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; IoTDB supports JOIN two time series 
ON their timestamp, i.e.,
&gt; &gt; &gt;&gt; time
&gt; &gt; &gt;&gt; &gt; &gt; &gt; series
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; alignment.
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; 2. I think the cluster version is 
coming... In the coming
&gt; &gt; &gt;&gt; v0.12, I
&gt; &gt; &gt;&gt; &gt; &gt; &gt; think
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; a
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; cluster version (that does
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; not support dynamically scale-out) 
will be released, and the
&gt; &gt; &gt;&gt; &gt; &gt; scale-out
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; feature may be released in v0.13 or 
0.14.
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; Best,
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; -----------------------------------
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; Xiangdong Huang
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; School of Software, Tsinghua 
University
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; Dhruv Garg <[email protected]> 
于2021年3月2日周二 下午3:54写道：
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt; Hello,
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt; I am a researcher and a new 
user of IoTDB. I was excited
&gt; &gt; when
&gt; &gt; &gt;&gt; I
&gt; &gt; &gt;&gt; &gt; saw
&gt; &gt; &gt;&gt; &gt; &gt; &gt; the
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt; performance numbers of IoTDB 
compared to InfluxDB, and I
&gt; &gt; have
&gt; &gt; &gt;&gt; set
&gt; &gt; &gt;&gt; &gt; &gt; it
&gt; &gt; &gt;&gt; &gt; &gt; &gt; up
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; on
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt; a few workstations and a 
Raspberry Pi device.
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt; I am becoming familiar with it 
and have a few queries:
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt;    1. Does IoTDB support Join 
queries? - I tried looking up
&gt; &gt; &gt;&gt; some
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt;    documentation on it, but 
could not find any. If the
&gt; &gt; &gt;&gt; feature is
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt; available,
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt;    could you please provide a 
sample query or the predicates
&gt; &gt; &gt;&gt; for
&gt; &gt; &gt;&gt; &gt; &gt; join
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt; queries?
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt;    2. I also browsed through 
the IoTDB Jira issues page. I
&gt; &gt; saw
&gt; &gt; &gt;&gt; &gt; that
&gt; &gt; &gt;&gt; &gt; &gt; &gt; the
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt;    distributed / cluster 
version is being developed.
&gt; &gt; &gt;&gt; &gt; Approximately
&gt; &gt; &gt;&gt; &gt; &gt; &gt; how
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; much
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt;    time would it take to get a 
stable / working version of
&gt; &gt; the
&gt; &gt; &gt;&gt; &gt; &gt; &gt; cluster
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt; IoTDB?
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt; It would be great if someone 
can respond to my queries. I
&gt; &gt; &gt;&gt; would
&gt; &gt; &gt;&gt; &gt; be
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt; waiting for a reply. Thanks in 
advance.
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt; Regards,
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt; dgargcs
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt; &gt;
&gt; &gt; &gt;&gt; &gt;
&gt; &gt; &gt;&gt;
&gt; &gt; &gt;
&gt; &gt;
</[email protected]></eof></[email protected]></[email protected]></[email protected]></[email protected]></[email protected]></[email protected]></[email protected]></[email protected]>

Re: Re: New user of IoTDB - a few queries

Reply via email to