# MultiView Independent Component Analysis with Delays

Ambroise Heurtebise  Université Paris-Saclay, Inria, CEA, Palaiseau, France
Pierre Ablin  Apple, Paris, France  Alexandre Gramfort  Université Paris-
Saclay, Inria, CEA, Palaiseau, France

###### Abstract

Linear Independent Component Analysis (ICA) is a blind source separation
technique that has been used in various domains to identify independent latent
sources from observed signals. In order to obtain a higher signal-to-noise
ratio, the presence of multiple views of the same sources can be used. In this
work, we present MultiView Independent Component Analysis with Delays
(MVICAD). This algorithm builds on the MultiView ICA model by allowing sources
to be delayed versions of some shared sources: sources are shared across views
up to some unknown latencies that are view- and source-specific. Using
simulations, we demonstrate that MVICAD leads to better unmixing of the
sources. Moreover, as ICA is often used in neuroscience, we show that
latencies are age-related when applied to Cam-CAN, a large-scale
magnetoencephalography (MEG) dataset. These results demonstrate that the
MVICAD model can reveal rich effects on neural signals without human
supervision.

##  1 Introduction

Independent Component Analysis (ICA) allows to separate mixed signals without
knowing the mixing operator [Comon,, 1994; Hyvärinen and Oja,, 2000]. In
linear ICA [Hyvarinen,, 1999; Ablin et al.,, 2018], the mixing is assumed
linear and the goal is to find a linear transformation of the mixed signals
that maximizes the statistical independence of the resulting sources. The
parameters of the transformation matrix are estimated by optimizing a cost
function, such as a likelihood.

In real-world data, one can sometimes access multiple views from the same
data. For example, a doctor may have an MRI scan, a CT scan, and the answers
to a clinical questionnaire for a diseased patient. In the context of ICA, a
naive method would be to use ICA on each view and compare the resulting
sources, but this solution would not take advantage of the group structure.
Thus, many multi-view ICA methods have been developed to take advantage of
multiple data views and increase the signal-to-noise ratio of the obtained
sources (see Section 2). As multiple views are used to obtain a better
recovery of the sources, it is typically assumed that the sources are the same
for all views.

MultiView Independent Component Analysis (MVICA) [Richard et al.,, 2020] is an
algorithm that makes the latter assumption. It aims to jointly estimate the
transformation matrices for multiple views while assuming that they all share
the exact same sources. While MVICA guarantees the identifiability of its
model and produces state-of-the-art results for several machine learning
tasks, it assumes that the sources are perfectly identical and temporally
aligned between views. In other words, MVICA ignores the view-specific
variability of the sources, which can lead to suboptimal separation results
for certain applications.

This kind of inter-view variability typically arises in neuroscience, a
critical application of ICA. Indeed, ICA has been extensively used for the
analysis of electroencephalography (EEG) [Makeig et al.,, 1995] and
magnetoencephalography (MEG) signals [Vigário et al.,, 2000], which are non-
invasive recordings of the electrical potentials and magnetic fields produced
by neurons in the brain. Since these signals are a mixture of the brain’s
complex neural activity, ICA has been successfully used to separate out the
different sources of activity. In this context, using data from multiple
subjects at once can reveal better insights about brain functional
organization. Yet, neural sources can present temporal delays between
different subjects due to individual differences in brain anatomy and
functional connectivity [Price et al.,, 2017; Roberts et al.,, 2010]. These
delays can affect the performance of standard multi-view ICA algorithms, as
they assume that the sources are uncorrelated and have the exact same temporal
structure across subjects.

In this work, building on the MVICA model, our goal is to develop an ICA
algorithm that is robust to temporal delays. Specifically, we propose a novel
method, called MultiView Independent Component Analysis with Delays (MVICAD),
that incorporates an iterative temporal alignment step in the estimation
procedure. We demonstrate the effectiveness of our method using simulations
and MEG data, showing that it outperforms the MVICA algorithm. Besides, we
report that the estimated delays of certain sources are related to age.

##  2 Related works

Many ICA algorithms have been proposed to process data from multiple datasets
at once. They vary by their assumptions: some methods assume that the datasets
have the same dimension, while others consider that all datasets share the
same sources. In this section, we describe some of these algorithms.

MVICA [Richard et al.,, 2020] is our reference algorithm. It is designed to
identify shared independent components across multiple datasets. It assumes
that the sources in each dataset are independent and that the shared sources
have the same temporal patterns across datasets. Unlike some other multi-view
ICA approaches, MVICA’s likelihood can be written in closed form and is then
optimized with a quasi-Newton algorithm, using the gradient and an
approximation of the Hessian of the likelihood. Since comparisons with other
multi-view ICA methods have been made in [Richard et al.,, 2020], we compare
our method to MVICA.

Group ICA refers to a whole category of ICA algorithms for processing multiple
views of the same data. It usually consists in first applying Principal
Component Analysis (PCA) on each view separately. Then all the resulting sets
are assembled into one set, using either concatenation or multi-set CCA
[Kettenring,, 1971; Correa et al.,, 2010], and PCA is used again on the
obtained set. Finally, an ICA algorithm is used. Concatenation can be either
spatial or temporal: temporal concatenation produces individual sources and a
common mixing matrix, whereas spatial concatenation gives common sources and
individual mixing matrices. Since we look for shared sources, we can compare
our method to Group ICA with spatial concatenation [Calhoun et al.,, 2009].
Adding a back-reconstruction step is also possible to recover individual
sources. However, this method does not bring statistical guarantees like
consistency or asymptotic efficiency, contrary to MVICA. It also gives poorer
results than MVICA on several neuroimaging tasks [Richard et al.,, 2020]. One
strength of CCA-based ICA [Varoquaux et al.,, 2009; Tsatsishvili et al.,,
2015] is that they project multiple datasets onto a common underlying space by
maximizing correlations between sets, which can be used for data fusion. Some
of these methods can handle datasets with different numbers of sources.
However, taking temporal delays into account in Group ICA would only be
possible in the assembling step (done with PCA or multi-set CCA), which is
less explicit than doing it during ICA optimization as we do in this paper.

Shared and Individual Multiview Independent Component Analysis [Pandeva and
Forré,, 2022] can identify both shared and individual sources at the same
time. This method assumes that the sources are independent within each dataset
but allows for both shared and individual sources to contribute to the data.
It uses a joint estimation approach to simultaneously estimate the shared and
individual components and their corresponding spatial and temporal maps.
However, the number of shared components needs to be specified, which can be
difficult.

##  3 METHOD

Notation The absolute value of the determinant of a matrix $W$ is $|W|$. The
$\ell_{2}$ norm of a vector $\mathbf{s}$ is $\|\mathbf{s}\|$, and $\|S\|$ is
the Frobenius norm of a matrix $S$. For a scalar valued function $f$ and a
matrix $S\in\mathbb{R}^{p\times n}$, we write
$f(S)=\sum_{j=1}^{p}\sum_{i=1}^{n}f(S_{ji})$.

###  3.1 Model and likelihood

Given $m$ views and $p$ sources, we model the observed signals
$X^{i}\in\mathbb{R}^{p\times n}$, $i=1,\dots,m$, as a linear combination of
shared but delayed sources plus noise. Here $n$ is the number of observations
(assumed to be common to each view) and $p$ is the number of features,
typically corresponding to the number of channels for multivariate signals.
The model is:

$X^{i}=A^{i}(\mathcal{T}_{{\boldsymbol{\tau}}^{i}}(S)+N^{i})\enspace,\quad
i=1,\dots,m\enspace,$ (1)

where the $A^{i}\in\mathbb{R}^{p\times p}$ are view-specific mixing matrices,
assumed to be full-rank, $\mathcal{T}$ is a shift operator that delays sources
across samples, $S\in\mathbb{R}^{p\times n}$ are the shared sources, the
vector
${\boldsymbol{\tau}}^{i}\in\\{-{\boldsymbol{\tau}}_{\max},\dots,{\boldsymbol{\tau}}_{\max}\\}^{p}$
contains the integer delays for the $p$ sources for a given view and
$N^{i}\in\mathbb{R}^{p\times n}$ are the noise matrices. In practice, if there
are $2$ sources and delays are $\\{1,2\\}$, the shift operator will shift to
the right the first source by $1$ sample and the second source by $2$ samples.
The hyperparameter ${\boldsymbol{\tau}}_{\max}$ thus represents the maximum
shift allowed. These delays represent a temporal shift rather than a time
distortion, so they correspond to what is called “constant delays” and not to
“cumulative delays” in [Roberts et al.,, 2010].

Figure 1: Sources of 2 views. We observe different delays $\tau^{i}_{j}$ for
each view $i$ and each source $j$. Blue curves represent the first view’s
sources and orange ones represent the sources of the second view. Left: first
source. Right: second source.

Figure 1 aims to help understand how delays are modeled. It shows how delay
can differ for every source and every view: in the figure, the first view has
a first source that peaks later than the second view but has an earlier peak
for the second source. Also, we observe that the parameters $\tau^{i}_{j}$,
where $i$ is the view and $j$ is the source, model the delay between sources
and the average source, represented in a dotted grey line.

As usual in ICA, we assume that observations are i.i.d., and so are the
sources. Also, we assume that the noise is Gaussian decorrelated of variance
$\sigma^{2}$ and independent across views and from the sources. In practice,
thanks to MVICA’s robustness to noise misspecification [Richard et al.,,
2020], estimating the noise level is not critical, so we usually set it to 1.

Importantly, our model is identifiable (see Appendix A) up to scale and
permutation for the mixing matrices and up to a common delay for the estimated
delays. Therefore recovering the sources is a well-posed problem.

We insist on the fact that we represent signals as matrices containing all
time samples instead of representing them as vectors. We made that choice
because of the use of the circular shift operator $\mathcal{T}$. Indeed, here
we assume periodic boundary conditions, so this operator takes as inputs $p$
signals of length $n$ and $p$ integer delays and rolls each signal by its
corresponding delay. Thus, we prefer to consider all observations at once.
Having periodic boundary conditions on real signals can sometimes produce
unwanted abrupt discontinuities when signals are noisy at the edges of the
time interval. This however can be mitigated by applying a windowing function
pushing signals to zero at the edges. In particular, this is appropriate in
neuroscience for EEG or MEG signals because sources are supposed to emerge
from baseline level and go back to baseline at the end of the period of
interest.

We derive a maximum-likelihood approach to recover the parameters of the
model, in a very similar way to [Richard et al.,, 2020]. As usual in ICA,
sources reconstruction is done by estimating first the unmixing matrices
$W^{i}=(A^{i})^{-1}\in\mathbb{R}^{p\times p}$. Thus, we see the likelihood as
a function of $\mathbf{W}=\\{W^{1},\dots,W^{m}\\}$ and
${\boldsymbol{\tau}}=\\{{\boldsymbol{\tau}}^{1},\dots,{\boldsymbol{\tau}}^{m}\\}$.
The negative log-likelihood can be written as:

$\mathcal{L}(\mathbf{W},\boldsymbol{{\boldsymbol{\tau}}})=-\sum_{i=1}^{m}\log|W^{i}|+\frac{1}{2\sigma^{2}}\sum_{i=1}^{m}\|Y^{i}-\bar{S}\|^{2}+f(\bar{S}),$
(2)

where $Y^{i}=\mathcal{T}_{-{\boldsymbol{\tau}}^{i}}(W^{i}X^{i})$ are the
aligned estimated sources of subject $i$,
$\bar{S}=\frac{1}{m}\sum_{i=1}^{m}Y^{i}$ are the average estimated sources and
$f$ is a smoothed version of the logarithm of the source density $d$ by
convolution with a Gaussian kernel. As explained in [Jung et al.,, 1997], the
density $d$ does not need to be known and can be estimated easily as soon as
we know if sources are sub- or super-Gaussian. In neuroscience, we commonly
assume that neural sources are super-Gaussian.

###  3.2 Unmixing matrix optimization

Optimizing Equation (2) with respect to all parameters at once is complex
since the variables ${\boldsymbol{\tau}}^{i}$ are discrete, while the $W^{i}$
are continuous, so we choose to minimize it iteratively by block coordinate
descent. First, $\mathcal{L}$ is alternatively minimized with respect to each
$W^{i}$. Subsequently, the minimization process takes place alternately with
respect to each $\tau^{i}$, and so forth.

By applying $\mathcal{T}_{{\boldsymbol{\tau}}^{i}}(\cdot)$ in the squared norm
and in function $f$, optimization of $\mathcal{L}$ with respect to $W^{i}$
boils down to minimizing:

$\displaystyle\mathcal{L}^{i}(W^{i})$ $\displaystyle=-n\>\log|W^{i}|$
$\displaystyle+\frac{m-1}{2m\sigma^{2}}\left\|W^{i}X^{i}-\sum_{j\neq
i}\frac{\mathcal{T}_{{\boldsymbol{\tau}}^{i}-{\boldsymbol{\tau}}^{j}}(W^{j}X^{j})}{m-1}\right\|^{2}$
$\displaystyle+f\left(\frac{1}{m}W^{i}X^{i}+\frac{1}{m}\sum_{j\neq
i}\mathcal{T}_{{\boldsymbol{\tau}}^{i}-{\boldsymbol{\tau}}^{j}}(W^{j}X^{j})\right).$

This formula strongly looks like Equation (4) in [Richard et al.,, 2020].
Indeed, the only difference lies in the fact that we obtained
$\mathcal{T}_{{\boldsymbol{\tau}}^{i}-{\boldsymbol{\tau}}^{j}}(W^{j}X^{j})$
instead of $W^{j}X^{j}$ in both sums over $j$. Intuitively, in the squared
norm, we compare $W^{i}X^{i}$ to the quantity $\frac{1}{m-1}\sum_{j\neq
i}\mathcal{T}_{{\boldsymbol{\tau}}^{i}-{\boldsymbol{\tau}}^{j}}(W^{j}X^{j})$,
where the last term is the shared source estimate of all subjects but subject
$i$, deliberately delayed by subject $i$’s delays. Having similar formulas
allows us to derive the relative gradient expression and Hessian approximation
of $\mathcal{L}$ with respect to $W^{i}$ immediately. To do so, we need to
replace the estimated sources $W^{j}X^{j}$ of subject $j\neq i$ by its shifted
version
$\mathcal{T}_{{\boldsymbol{\tau}}^{i}-{\boldsymbol{\tau}}^{j}}(W^{j}X^{j})$ in
the formulas of gradient $G^{i}\in\mathbb{R}^{p\times p}$ and Hessian
approximation $H^{i}\in\mathbb{R}^{p\times p\times p\times p}$ in [Richard et
al.,, 2020]. Then, we use a quasi-Newton approach by computing a direction
$D=-(H^{i})^{-1}G^{i}$ and finding with line search a step size $\rho$ such
that $\mathcal{L}^{i}((I_{p}+\rho D)W^{i})<\mathcal{L}^{i}(W^{i})$.

###  3.3 Delay optimization

Equation 2 should be optimized with respect to ${\boldsymbol{\tau}}^{i}$.
Because of its complex form, the term $f(\bar{S})$ is burdensome to minimize
rapidly, so we chose to discard it. Indeed, minimization of the true loss
function would require an exhaustive search that has a computational
complexity of $o({\boldsymbol{\tau}}_{\max}\times n)$, while using only the
squared norm part will allow us to use cross-correlation to quickly compute
the error, hence a computational complexity of
$o({\boldsymbol{\tau}}_{\max}\times\log(n))$. As the first term,
$-\sum_{i=1}^{m}\log|W^{i}|$, is independent from ${\boldsymbol{\tau}}^{i}$,
the only part that needs to be minimized is:

$\mathcal{L}^{i}{}^{\prime}({\boldsymbol{\tau}}^{i})=\sum_{i=1}^{m}\|Y^{i}-\bar{S}\|^{2}=\sum_{i=1}^{m}\sum_{j=1}^{p}\|\mathbf{y}^{i}_{j}-\bar{\mathbf{s}}_{j}\|^{2}\enspace,$

where $\mathbf{y}^{i}_{j}\in\mathbb{R}^{n}$ (resp.
$\bar{\mathbf{s}}_{j}\in\mathbb{R}^{n}$) is the $j$-th row of $Y^{i}$ (resp.
$\bar{S}$). Since $\mathbf{y}^{i}_{j}-\bar{\mathbf{s}}_{j}$ only depends on
${\boldsymbol{\tau}}^{i}_{j}$, and not on ${\boldsymbol{\tau}}^{i}_{k}$ for
$k\neq j$, we can minimize $\mathcal{L}^{i}{}^{\prime}$ with respect to each
${\boldsymbol{\tau}}^{i}_{j}$ separately.

Thus, optimization boils down to finding, for each source
$j\in\\{1,\dots,p\\}$ separately, the integer ${\boldsymbol{\tau}}^{i}_{j}$
that maximizes $\langle\mathbf{y}^{i}_{j},\mathbf{y}^{-i}_{j}\rangle$, where
$\mathbf{y}^{-i}_{j}=\frac{1}{m-1}\sum_{k\neq i}\mathbf{y}^{k}_{j}$. This step
is simply done by cross-correlation. Stacking the results in a vector gives
${\boldsymbol{\tau}}^{i}=\\{{\boldsymbol{\tau}}^{i}_{1},\dots,{\boldsymbol{\tau}}^{i}_{p}\\}$
and concludes the delay optimization step of subject $i$.

Note that we have to set a maximum delay parameter
${\boldsymbol{\tau}}_{\max}$ in MVICAD. This parameter represents the maximum
delay possible, in absolute value, for each source and each subject, that our
algorithm will test. In the neuroscience application, there are natural
choices for ${\boldsymbol{\tau}}_{\max}$ (see Section 4.2).

Furthermore, we compared on simulations our method with an exhaustive search
that includes the last term of Equation 2 in delay optimization. We observed
that ignoring this term does not affect performance.

##  4 EXPERIMENTS

All the code is written in Python and is available on our GitHub repo.

###  4.1 Simulation study

To evaluate the performance of the MVICAD algorithm, we conducted a number of
simulations allowing us to quantify the error in parameter estimation.

In synthetic experiments, we simulate data according to our model (1). We use
$5$ subjects, $3$ sources, and $700$ samples, as this number of samples is
comparable to the one available for MEG data (See below).

Figure 2: Left: true generated shared sources. Middle: sources estimated by
MVICA. Right: sources estimated by MVICAD.

In Figure 2, we also use a maximum delay parameter of $40$, as this number
corresponds to $40$ ms if the sampling rate is $1000$ Hz, which is the typical
maximum amount of delay in the context of MEG signals used in real data
experiments [Price et al.,, 2017]. This figure (left) shows the true simulated
shared sources. In the middle, we observe that sources found by MVICA do not
fit these true sources, contrary to the sources found by MVICAD (right).

####  4.1.1 Amari distance

As classically done for ICA algorithms, we use the Amari distance [Moreau and
Macchi,, 1998] to quantify the error on mixing matrices. The benefit of this
metric is that it is scale and permutation invariant. Figure 3 represents the
Amari distance of both MVICA and MVICAD for several levels of delay introduced
in the model. We see that our algorithm outperforms MVICA in this particular
setup, as soon as there is some delay. In fact, the curves seem to be
approximately linear. They start from the same point when there is no delay,
and when there is a delay of $40$, MVICA’s Amari distance is $1.18$, whereas
the one of MVICAD is equal to $0.68$.

Note that, for each level of delay in the $x$-axis, the maximum delay
parameter of MVICAD is set to this level of delay precisely. Consequently,
both algorithms have the same Amari distance when the delay equals $0$.
Indeed, when the maximum delay parameter is set to $0$, our algorithm strictly
corresponds to MVICA.

####  4.1.2 Artificial delays

Our next experiment illustrates the ability of the model to recover delays. We
used $40$ subjects, $5$ sources, $700$ samples, a maximum delay parameter of
$40$, and a quantity of noise that gave a signal-to-noise ratio approximately
equal to 5.

Figure 4 shows the results of this experiment. In total, there are $40\times
5=200$ estimated delays. We observe that estimated delays are strongly
correlated with artificial delays: the slope of the fit function is almost
$1$, and so does the $R^{2}$ score.

Note that not having a slope of exactly $1$ can be partially explained by the
presence of the noise. Searching a delay for each source of each subject means
having $mp$ delay parameters and is thus sensitive to noise. Also, it can be
explained by the fact that the number of samples is rather low ($700$).
Nevertheless, this experiment shows the ability of MVICAD to retrieve the true
delays.

Figure 3: Amari distance of both MVICA and MVICAD for several levels of delay.
We observe that our algorithm outperforms MVICA as soon as some delay is
introduced in the model.

###  4.2 Real data experiments

In order to test MVICAD on real data, we use the Cam-CAN dataset. In this
dataset delays usually do not exceed $60$ ms for visual tasks and $20$ ms for
auditory tasks [Price et al.,, 2017]. Since the sampling rate is equal to
$1000$ Hz in the data, we set ${\boldsymbol{\tau}}_{\max}$ to $60$ (number of
samples).

####  4.2.1 Cam-CAN dataset

The Cam-CAN dataset [Taylor et al.,, 2017] is a large and comprehensive
dataset of neuroimaging, cognitive, and demographic data collected from a
group of healthy adults. The dataset includes data from $661$ participants,
ranging in age from 18 to 88 years old, with equal numbers of participants in
each $10$-year age range. The participants were recruited from the general
population in the Cambridgeshire area of the UK, with the aim of recruiting a
sample that was representative of the local population in terms of age, sex,
and education level.

This dataset is freely available to researchers and has become a resource for
investigating the neurobiological mechanisms underlying healthy aging and age-
related cognitive decline.

The Cam-CAN Stage 2 repository contains a subset of data from the larger Cam-
CAN dataset, focused on the second wave of assessments conducted approximately
two years after the initial assessment. The MEG data in the Stage 2 repository
includes recordings of brain activity using a whole-head $306$ channel Elekta
Neuromag Vectorview system. It contains data from several cognitive tasks,
including visual and auditory tasks. For each of these tasks, participants
were presented a series of stimuli, and averaging the obtained signals give
what is called evoked data.

In our experiments, we focus on evoked data of both tasks. We had to
preprocess data and remove subjects with unusual signals, which reduced the
number of subjects for the visual and auditory tasks. Also, these data last
$0.7$ second at a sampling rate of $1000$ Hz, thus giving datasets of shape
$(477,306,701)$ for the visual task and $(501,306,701)$ for the auditory one.

Figure 4: Scatter plot of artificial and estimated delays. We used $40$
subjects and $5$ sources, and each point represents one source of one subject.

Figure 5: Scatter plot of age and delay of the $477$ subjects of the visual
dataset. We observe a significant correlation between aging and estimated
delay.

####  4.2.2 Delay and age correlation

The next experiment consists in studying age-delay correlation, as Cam-CAN
data are often used to investigate mechanisms underlying healthy aging and
age-related cognitive decline.

Figure 5 shows the scatter plot of age and delay of the 477 subjects of the
visual task. This scatter plot shows a significant correlation between the age
of subjects and estimated delays. To produce it, we first applied a PCA with
$10$ components on the dataset, whose shape became $(477,10,701)$, and then
used MVICAD to get estimated delays of shape $(477,10)$. We report here the
source whose delays are most linearly related to age (using $R^{2}$ score, and
p-value of Pearson correlation). From a biological point of view, having
relevant and non-relevant sources in terms of age-delay correlation makes
sense because some of the $10$ obtained sources represent noise, whereas
others are closer to the actual neural signals affected by aging.

However, we can also investigate the model with only one delay per subject. In
other words, we force estimated delays to be equal across sources in our
algorithm, i.e. we only estimate one delay per subject. This method reveals
some aging effects without having to visually explore delays of each source,
yet it is not source-specific. The obtained results are: slope $=0.08$ ;
$R^{2}=0.08$ ; p-value $=9.69\times 10^{-11}$. The drawback of having one
delay per subject is that it produces a higher negative log-likelihood and
poorer source reconstruction.

Finally, note that this experiment produces lower results for the auditory
task. It was expected since auditive stimuli are known to present “cumulative
delays” instead of “constant delays” [Roberts et al.,, 2010].

##  5 CONCLUSION

We have proposed an improvement of the algorithm MVICA which aims to retrieve
latent sources from multiple views and can be used in various domains. To do
so, we estimated delays that appear in each source of each subject and use
them to correct our source reconstruction during optimization. We presented
the likelihood of our model in closed form and proposed a way of minimizing it
with respect to both unmixing matrices and delays. In the context of MEG
signals, we showed that our estimated delays were significantly correlated to
age and demonstrated on a synthetic experiment that our method outperforms
MVICA in terms of Amari distance.

##  6 ACKNOWLEDGEMENT

Data collection and sharing for this project was provided by the Cambridge
Centre for Ageing and Neuroscience (CamCAN). CamCAN funding was provided by
the UK Biotechnology and Biological Sciences Research Council (grant number
BB/H008217/1), together with support from the UK Medical Research Council and
University of Cambridge, UK. Data used in the preparation of this work were
obtained from the CamCAN repository (available at http://www.mrc-
cbu.cam.ac.uk/datasets/camcan/). This work was supported by the ANR BrAIN
(ANR-20-CHIA0016) grant.

## References

  * Ablin et al., [2018] Ablin, P., Cardoso, J.-F., and Gramfort, A. (2018).  Faster independent component analysis by preconditioning with hessian approximations.  IEEE Transactions on Signal Processing, 66(15):4040–4049.
  * Calhoun et al., [2009] Calhoun, V. D., Liu, J., and Adalı, T. (2009).  A review of group ICA for fMRI data and ICA for joint inference of imaging, genetic, and ERP data.  Neuroimage, 45(1):S163–S172.
  * Comon, [1994] Comon, P. (1994).  Independent component analysis, a new concept?  Signal processing, 36(3):287–314.
  * Correa et al., [2010] Correa, N. M., Adali, T., Li, Y.-O., and Calhoun, V. D. (2010).  Canonical correlation analysis for data fusion and group inferences.  IEEE signal processing magazine, 27(4):39–50.
  * Hyvarinen, [1999] Hyvarinen, A. (1999).  Fast and robust fixed-point algorithms for independent component analysis.  IEEE transactions on Neural Networks, 10(3):626–634.
  * Hyvärinen and Oja, [2000] Hyvärinen, A. and Oja, E. (2000).  Independent component analysis: algorithms and applications.  Neural networks, 13(4-5):411–430.
  * Jung et al., [1997] Jung, T.-P., Humphries, C., Lee, T.-W., Makeig, S., McKeown, M., Iragui, V., and Sejnowski, T. J. (1997).  Extended ICA removes artifacts from electroencephalographic recordings.  Advances in neural information processing systems, 10.
  * Kettenring, [1971] Kettenring, J. R. (1971).  Canonical analysis of several sets of variables.  Biometrika, 58(3):433–451.
  * Makeig et al., [1995] Makeig, S., Bell, A., Jung, T.-P., and Sejnowski, T. J. (1995).  Independent component analysis of electroencephalographic data.  Advances in neural information processing systems, 8.
  * Moreau and Macchi, [1998] Moreau, E. and Macchi, O. (1998).  Self-adaptive source separation. ii. comparison of the direct, feedback, and mixed linear network.  IEEE transactions on signal processing, 46(1):39–50.
  * Pandeva and Forré, [2022] Pandeva, T. and Forré, P. (2022).  Multi-view independent component analysis with shared and individual sources.  arXiv preprint arXiv:2210.02083.
  * Price et al., [2017] Price, D., Tyler, L. K., Neto Henriques, R., Campbell, K. L., Williams, N., Treder, M. S., Taylor, J. R., and Henson, R. (2017).  Age-related delay in visual and auditory evoked responses is mediated by white-and grey-matter differences.  Nature communications, 8(1):15671.
  * Richard et al., [2020] Richard, H., Gresele, L., Hyvarinen, A., Thirion, B., Gramfort, A., and Ablin, P. (2020).  Modeling shared responses in neuroimaging studies through multiview ICA.  Advances in Neural Information Processing Systems, 33:19149–19162.
  * Roberts et al., [2010] Roberts, T. P., Khan, S. Y., Rey, M., Monroe, J. F., Cannon, K., Blaskey, L., Woldoff, S., Qasmieh, S., Gandal, M., Schmidt, G. L., et al. (2010).  MEG detection of delayed auditory evoked responses in autism spectrum disorders: towards an imaging biomarker for autism.  Autism Research, 3(1):8–18.
  * Taylor et al., [2017] Taylor, J. R., Williams, N., Cusack, R., Auer, T., Shafto, M. A., Dixon, M., Tyler, L. K., Henson, R. N., et al. (2017).  The Cambridge Centre for Ageing and Neuroscience (Cam-CAN) data repository: Structural and functional MRI, MEG, and cognitive data from a cross-sectional adult lifespan sample.  neuroimage, 144:262–269.
  * Tsatsishvili et al., [2015] Tsatsishvili, V., Cong, F., Toiviainen, P., and Ristaniemi, T. (2015).  Combining PCA and multiset CCA for dimension reduction when group ICA is applied to decompose naturalistic fMRI data.  In 2015 International Joint Conference on Neural Networks (IJCNN), pages 1–6. IEEE.
  * Varoquaux et al., [2009] Varoquaux, G., Sadaghiani, S., Poline, J. B., and Thirion, B. (2009).  CanICA: Model-based extraction of reproducible group-level ICA patterns from fMRI time series.  arXiv preprint arXiv:0911.4650.
  * Vigário et al., [2000] Vigário, R., Sarela, J., Jousmiki, V., Hamalainen, M., and Oja, E. (2000).  Independent component approach to the analysis of EEG and MEG recordings.  IEEE transactions on biomedical engineering, 47(5):589–593.

##  Appendix A Appendix

Let us prove the identifiability of our model. We consider multiple i.i.d.
r.v. $\mathbf{s}_{k}\in\mathbb{R}^{p}$,
$k=1,\dots,{\boldsymbol{\tau}}_{\max}$, that model sources, instead of only
one r.v. $\mathbf{s}$, as is often the case. Having multiple variables allows
us to delay them with operator $\mathcal{T}$. For simplicity, we define
$\mathbf{s}:=\mathbf{s}_{1}$. Recall that our model is:

$\mathbf{x}^{i}=A^{i}(\mathcal{T}_{{\boldsymbol{\tau}}^{i}}(\mathbf{s})+\mathbf{n}^{i})\enspace,\quad
i=1,\dots,m\enspace,$

with ${\boldsymbol{\tau}}^{i}\in\mathbb{R}^{p}$. Note that, compared to
Equation (1), we used vectors instead of matrices. Assume that we also have:

$\mathbf{x}^{i}=A^{\prime i}(\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
i}}(\mathbf{s}^{\prime})+\mathbf{n}^{\prime i})\enspace,\quad
i=1,\dots,m\enspace,$

for some $A^{\prime i},{\boldsymbol{\tau}}^{\prime i},\mathbf{s}^{\prime}$ and
$\mathbf{n}^{\prime i}$. Note that we also define
$\mathbf{s}^{\prime}:=\mathbf{s}^{\prime}_{1}$ where
$\mathbf{s}^{\prime}_{k}\in\mathbb{R}^{p}$,
$k=1,\dots,{\boldsymbol{\tau}}_{\max}$, are i.i.d. r.v.

Since we assume that $\mathbf{s}_{k}$ has non-Gaussian independent components
(whose densities are not reduced to a point-like mass) and that the r.v.
$\mathbf{s}_{k}$ are i.i.d. and that $\mathbf{n}^{i}$ is Gaussian
decorrelated, then
$\mathcal{T}_{{\boldsymbol{\tau}}^{i}}(\mathbf{s})+\mathbf{n}^{i}$ also has
non-Gaussian independent components. And so does
$\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
i}}(\mathbf{s}^{\prime})+\mathbf{n}^{\prime i}$. Following [Comon,, 1994],
Theorem $11$, there exists a scale-permutation matrix $P^{i}$ such that
$A^{\prime i}=A^{i}P^{i}$. As a consequence, and since $A^{i}$ is invertible,
we have, for all $i$:

$\displaystyle\mathcal{T}_{{\boldsymbol{\tau}}^{i}}(\mathbf{s})+\mathbf{n}^{i}$
$\displaystyle=P^{i}(\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
i}}(\mathbf{s}^{\prime})+\mathbf{n}^{\prime i})$
$\displaystyle\Rightarrow\mathbf{s}+\mathcal{T}_{-{\boldsymbol{\tau}}^{i}}(\mathbf{n}^{i})$
$\displaystyle=\mathcal{T}_{-{\boldsymbol{\tau}}^{i}}\left(P^{i}(\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
i}}(\mathbf{s}^{\prime})+\mathbf{n}^{\prime i})\right)$
$\displaystyle=P^{i}(\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
i}-(P^{i})^{\top}{\boldsymbol{\tau}}^{i}}(\mathbf{s}^{\prime})-\mathcal{T}_{-(P^{i})^{\top}{\boldsymbol{\tau}}^{i}}(\mathbf{n}^{\prime
i})).$

The last line comes from the equality
$\mathcal{T}_{{\boldsymbol{\tau}}}P=P\mathcal{T}_{P^{\top}{\boldsymbol{\tau}}}$
for a scale-permutation matrix $P$ and a vector ${\boldsymbol{\tau}}$.

We focus on subject $1$ and subject $i\neq 1$:

$\displaystyle\mathbf{s}+\mathcal{T}_{-{\boldsymbol{\tau}}^{1}}(\mathbf{n}^{1})-(\mathbf{s}+\mathcal{T}_{-{\boldsymbol{\tau}}^{i}}(\mathbf{n}^{i}))$
$\displaystyle=P^{1}(\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
1}-(P^{1})^{\top}{\boldsymbol{\tau}}^{1}}(\mathbf{s}^{\prime})+\mathcal{T}_{-(P^{1})^{\top}{\boldsymbol{\tau}}^{1}}(\mathbf{n}^{\prime
1}))$ $\displaystyle-P^{i}(\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
i}-(P^{i})^{\top}{\boldsymbol{\tau}}^{i}}(\mathbf{s}^{\prime})+\mathcal{T}_{-(P^{i})^{\top}{\boldsymbol{\tau}}^{i}}(\mathbf{n}^{\prime
i})).$

Consequently,

$\displaystyle P^{1}\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
1}-(P^{1})^{\top}{\boldsymbol{\tau}}^{1}}(\mathbf{s}^{\prime})-P^{i}\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
i}-(P^{i})^{\top}{\boldsymbol{\tau}}^{i}}(\mathbf{s}^{\prime})$
$\displaystyle=\mathcal{T}_{-{\boldsymbol{\tau}}^{1}}(\mathbf{n}^{1})-\mathcal{T}_{-{\boldsymbol{\tau}}^{i}}(\mathbf{n}^{i})$
$\displaystyle+P^{i}\mathcal{T}_{-(P^{i})^{\top}{\boldsymbol{\tau}}^{i}}(\mathbf{n}^{\prime
i})-P^{1}\mathcal{T}_{-(P^{1})^{\top}{\boldsymbol{\tau}}^{1}}(\mathbf{n}^{\prime
1}).$ (3)

Since the right-hand side of the last equality is a linear combination of
Gaussian random variables, this implies that
$P^{1}\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
1}-(P^{1})^{\top}{\boldsymbol{\tau}}^{1}}(\mathbf{s}^{\prime})-P^{i}\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
i}-(P^{i})^{\top}{\boldsymbol{\tau}}^{i}}(\mathbf{s}^{\prime})$ is also
Gaussian.

Let us show that there exists a vector ${\boldsymbol{\tau}}\in\mathbb{R}^{p}$
such that, for all $i$, ${\boldsymbol{\tau}}^{\prime
1}-(P^{1})^{\top}{\boldsymbol{\tau}}^{1}={\boldsymbol{\tau}}^{\prime
i}-(P^{i})^{\top}{\boldsymbol{\tau}}^{i}=:{\boldsymbol{\tau}}$. Note that this
is an equality modulo ${\boldsymbol{\tau}}_{\max}$. Indeed, delaying sources
by ${\boldsymbol{\tau}}$ and by
${\boldsymbol{\tau}}+q{\boldsymbol{\tau}}_{\max}$, $q\in\mathbb{Z}$, is
equivalent, according to our definition of the shift operator $\mathcal{T}$.
For simplicity, we omit the modulo part in equalities about delays.

By contradiction, let us suppose that ${\boldsymbol{\tau}}^{\prime
1}-(P^{1})^{\top}{\boldsymbol{\tau}}^{1}\neq{\boldsymbol{\tau}}^{\prime
i}-(P^{i})^{\top}{\boldsymbol{\tau}}^{i}$. Let us call
$C\subset\\{1,\dots,p\\}$ the set of indices such that
$({\boldsymbol{\tau}}^{\prime
1}-(P^{1})^{\top}{\boldsymbol{\tau}}^{1})_{C}=({\boldsymbol{\tau}}^{\prime
i}-(P^{i})^{\top}{\boldsymbol{\tau}}^{i})_{C}$ and let
$\bar{C}=\\{1,\dots,p\\}\backslash C$. By assumption, $\bar{C}\neq\emptyset$.
By definition of $C$, $\left(\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
1}-(P^{1})^{\top}{\boldsymbol{\tau}}^{1}}(\mathbf{s}^{\prime})\right)_{C}=\left(\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
i}-(P^{i})^{\top}{\boldsymbol{\tau}}^{i}}(\mathbf{s}^{\prime})\right)_{C}$,
and $\left(\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
1}-(P^{1})^{\top}{\boldsymbol{\tau}}^{1}}(\mathbf{s}^{\prime})\right)_{\bar{C}}$
and $\left(\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
i}-(P^{i})^{\top}{\boldsymbol{\tau}}^{i}}(\mathbf{s}^{\prime})\right)_{\bar{C}}$
are independent.

Thus, the left-hand side of Equation (3) only depends on three terms:
$\left(\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
1}-(P^{1})^{\top}{\boldsymbol{\tau}}^{1}}(\mathbf{s}^{\prime})\right)_{C}$,$\left(\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
1}-(P^{1})^{\top}{\boldsymbol{\tau}}^{1}}(\mathbf{s}^{\prime})\right)_{\bar{C}}$
and $\left(\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
i}-(P^{i})^{\top}{\boldsymbol{\tau}}^{i}}(\mathbf{s}^{\prime})\right)_{\bar{C}}$,
which are all three independent from each others.

The left-hand side of Equation (3) is thus a linear combination of independent
random variables and is Gaussian. So, by Cramér’s lemma, we should have that
$\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
1}-(P^{1})^{\top}{\boldsymbol{\tau}}^{1}}(\mathbf{s}^{\prime})$ and
$\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
i}-(P^{i})^{\top}{\boldsymbol{\tau}}^{i}}(\mathbf{s}^{\prime})$ are Gaussian
too, which is absurd, given that $\mathbf{s}^{\prime}$ is assumed to be non-
Gaussian. So, there exists a vector ${\boldsymbol{\tau}}$ such that
${\boldsymbol{\tau}}^{\prime
1}-(P^{1})^{\top}{\boldsymbol{\tau}}^{1}={\boldsymbol{\tau}}^{\prime
i}-(P^{i})^{\top}{\boldsymbol{\tau}}^{i}=:{\boldsymbol{\tau}}$.

Let us define $\mathbf{\tilde{s}}:=\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
1}-(P^{1})^{\top}{\boldsymbol{\tau}}^{1}}(\mathbf{s}^{\prime})=\mathcal{T}_{{\boldsymbol{\tau}}^{\prime
i}-(P^{i})^{\top}{\boldsymbol{\tau}}^{i}}(\mathbf{s}^{\prime})$. Since the
right-hand side of Equation (3) is Gaussian, it follows that
$(P^{1}-P^{i})\>\mathbf{\tilde{s}}$ is Gaussian too. Thus, the equality holds
only if $P^{1}=P^{i}$. Therefore, the matrices $P^{i}$ are all equal, and
there exists a scale-permutation matrix $P$ such that $A^{\prime i}=A^{i}P$.

In conclusion, we proved that there exists a scale and permutation matrix $P$
and a vector ${\boldsymbol{\tau}}\in\mathbb{R}^{p}$ such that, for all $i$,
$A^{\prime i}=A^{i}P$ and ${\boldsymbol{\tau}}^{\prime
i}=P^{\top}{\boldsymbol{\tau}}^{i}+{\boldsymbol{\tau}}$.


# Beyond Text-to-SQL for IoT Defense: A Comprehensive Framework for Querying
and Classifying IoT Threats

Ryan Pavlich1, Nima Ebadi2, Richard Tarbell1, Billy Linares1, Adrian Tan1,
Rachael Humphreys1, Jayanta Kumar Das1, Rambod Ghandiparsi1, Hannah Haley1,
Jerris George1, 3Rocky Slavin, 4Kim-Kwang Raymond Choo,
4Glenn Dietrich, and 4Anthony Rios
1Data Analytics, 2Department of Electrical and Computer Engineering,
3Department of Computer Science, 4Department of Information Systems and Cyber
Security
The University of Texas at San Antonio
{Ryan.Palvich<EMAIL_ADDRESS>

###### Abstract

Recognizing the promise of natural language interfaces to databases, prior
studies have emphasized the development of text-to-SQL systems. While
substantial progress has been made in this field, existing research has
concentrated on generating SQL statements from text queries. The broader
challenge, however, lies in inferring new information about the returned data.
Our research makes two major contributions to address this gap. First, we
introduce a novel Internet-of-Things (IoT) text-to-SQL dataset comprising
10,985 text-SQL pairs and 239,398 rows of network traffic activity. The
dataset contains additional query types limited in prior text-to-SQL datasets,
notably temporal-related queries. Our dataset is sourced from a smart
building’s IoT ecosystem exploring sensor read and network traffic data.
Second, our dataset allows two-stage processing, where the returned data
(network traffic) from a generated SQL can be categorized as malicious or not.
Our results show that joint training to query and infer information about the
data can improve overall text-to-SQL performance, nearly matching
substantially larger models. We also show that current large language models
(e.g., GPT3.5) struggle to infer new information about returned data, thus our
dataset provides a novel test bed for integrating complex domain-specific
reasoning into LLMs.

Beyond Text-to-SQL for IoT Defense: A Comprehensive Framework for Querying and
Classifying IoT Threats

Ryan Pavlich1, Nima Ebadi2, Richard Tarbell1, Billy Linares1, Adrian Tan1,
Rachael Humphreys1, Jayanta Kumar Das1, Rambod Ghandiparsi1, Hannah Haley1,
Jerris George1, 3Rocky Slavin, 4Kim-Kwang Raymond Choo, 4Glenn Dietrich, and
4Anthony Rios 1Data Analytics, 2Department of Electrical and Computer
Engineering, 3Department of Computer Science, 4Department of Information
Systems and Cyber Security The University of Texas at San Antonio
{Ryan.Palvich<EMAIL_ADDRESS>

##  1 Introduction

Relational databases contain vast quantities of structured knowledge, often
having trillions of rows of data, spanning diverse domains from healthcare and
finance to entertainment and education. While structured query languages (SQL)
provide database experts the resources to extract, manipulate, and reason over
this data, many potential users remain cut off from direct access due to the
steep learning curve of mastering these languages. The importance of making
data more accessible and actionable for a wider audience cannot be
understated, given the growing centrality of data-driven decision-making in
modern society. The vision of natural language interfaces to databases (NLIDB)
is rooted in this very imperative—to allow non-experts to interact with
databases using familiar, everyday language. This reinforces the importance of
developing modern text-to-SQL systems that can also reason over databases.

Figure 1: This figure provides an example of querying and reasoning over
network traffic data.

A system that seamlessly translates natural language queries into SQL (text-
to-SQL) not only democratizes access to data but also has the potential to
drastically reduce the time to insights for diverse stakeholders, including
managers, analysts, educators, and the general public. There have been many
advances in translating natural language to SQL Xu et al. (2017); Zhong et al.
(2017); Bogin et al. (2019); Wang et al. (2018); Yu et al. (2018a); Scholak et
al. (2021); Xie et al. (2022); Wang et al. (2022a); Chen et al. (2021); Sun et
al. (2022). Recent work has focused on either fine-tuning transformers or on
the use of pre-built large language models (e.g., ChatGPT) with prompt tuning
and in-context examples. For example, Pourreza and Rafiei (2023) explored in-
context learning using ChatGPT to generate SQL statements, and Dong et al.
(2023) explored zero-shot text-to-SQL generation using ChatGPT. Wang et al.
(2020a) developed a unified framework using fine-tuning for text-to-SQL
generation, leveraging relation-aware self-attention, to tackle schema
encoding, schema linking, and feature representation. Combined with BERT data
augmentation, this framework yielded a remarkable exact match accuracy of
65.6% on the Spider dataset.

Much of the prior work on text-to-SQL generation has focused on simply
generating SQL statements from the input text queries. Some recent work has
expanded on standard studies by exploring conversational text-to-SQL tasks Yu
et al. (2019). Intuitively, Yu et al. (2019) developed a system that can ask
follow-up questions to answer ambiguous queries better, verify returned
results, and notify users of unanswerable queries. However, there is limited
work that can query a database and make inferences (understand) the returned
data. Follow-up questions may involve making inferences and returning results
that are not directly within the database. Hence, translating natural language
to SQL is only half the challenge. The true power of such a system lies in its
ability to retrieve and infer new information about the data returned. This
ensures that the insights drawn from databases are accurate and meaningful.
For instance, in an educational context, a student might not only ask for the
number of historical events in a given time but might also want to know their
significance or interconnections, requiring a depth of reasoning beyond
retrieval.

At a high level, our work combines two lines of research not explored in
previous papers: tabular data classification and question answering using
transformers Badaro et al. (2023) and text-to-SQL generation. There has been
some recent work about predicting various aspects of tabular data. For
example, Yang and Zhu (2021) predicts whether a claim is true or false given
an input table. Likewise, Deng et al. (2022) developed a system to inform
missing or corrupted data within a table. However, much of this work assumes
the table is provided. Hence, we develop a new text-to-SQL dataset to make
predictions/inferences about the data and query the data using a single model.
An example of our task is provided in Figure 1. As a case study, our dataset
consists of Internet-of-Things (IoT) data from a smart building setting.
Specifically, we assume a centralized database that captures both network
traffic about the IoT devices and sensor readings (temperature, humidity, CO2
levels, etc.). The SQL statements query the IoT databases to return relevant
data. The reasoning component of our dataset is specific to the network data.
We classify the network traffic as malicious (e.g., DDoS attacks, botnet
activity, etc.) or benign (non-malicious activity). Our decision to use IoT
data is due to the following reasons. First, IoT data has a huge temporal
component Acar et al. (2020). There have been limited text-to-dataset
resources that contain many temporal-related queries (e.g., Spider is based on
SQLite databases and does not support datetime columns). Second, making
inferences about network traffic data is non-trivial and has not been explored
in the NLP community.

In summary, the contributions of this paper are as follows:  (i) We introduce
a new IoT-SQL dataset containing 10,985 unique text-SQL pairs and 239,398 rows
of network traffic activity from Zeek logs with annotations for malicious and
non-malicious activity (e.g., DDoS attacks). This dataset provides a new test
bed for text-to-SQL models and LLMs towards both querying data an actually
understanding it. Specifically, current state-of-the-art LLMs GPT3.5 fail to
perform well on this dataset for the reasoning component.111The dataset will
be released upon acceptance. (ii) We evaluate the performance of text-to-SQL
models that can jointly query and reason about the data (i.e., predict whether
specific network traffic is malicious). Our results suggest that modeling both
tasks together substantially improves text-to-SQL performance with limited
impact on network-traffic malicious activity detection.  (iii) We perform
error analysis and provide examples of how jointly training to query and
understand the data improved SQL generation.

##  2 Related Work

Text-to-SQL Datasets. Recent momentum has grown in evaluating text-to-SQL
systems, especially their generalizability, with less focus on the medical
domain. Text-to-SQL translates text into machine-readable formats. Several
datasets exist for this task: ATIS Dahl et al. (1994); Srinivasan Iyer and
Zettlemoyer (2017) (airline queries), Geography Zelle and Mooney (1996);
Srinivasan Iyer and Zettlemoyer (2017) (geographical data), Restaurants
Giordani and Moschitti (2013); Tang and Mooney (2000); Popescu et al. (2003)
(restaurant details), WikiSQL Zhong et al. (2017), Spider Yu et al. (2018b),
and IMDB and Yelp Navid Yaghmazadeh and Dillig (2017) (movie and business
data). The Spider dataset emerges as a cornerstone resource in the text-to-SQL
benchmarks landscape. Designed to evaluate text-to-SQL systems rigorously,
Spider boasts impressive extensiveness and diversity, featuring over 10,000
questions from over 200 databases. Its strength lies in its volume and the
complexity of its queries.

Recent efforts have also been made to develop new datasets beyond traditional
text-to-SQL pairs. Yu et al. (2019), for example, collected a conversation-
like corpus where a system can ask follow-up questions to answer ambiguous
queries better, verify returned results, and notify users of unanswerable
queries. Similarly, researchers have also focused on curating data (text-SQL
pairs) that capture items missing in previous datasets (e.g., temporal-related
queries). For example, Vo et al. (2022) introduced a new dataset called
TempQ4NLIDB that contains 389 temporal-related question-SQL pairs to overcome
limitations in existing datasets (e.g., Spider). Our research expands on this
work, containing more than 1,000 temporally-related queries using MySQL
datetime columns.

Text-to-SQL Methods. The field of text-to-SQL is concerned with automatically
translating natural language queries into structured SQL queries. Recent
advancements in neural network models have led to significant improvements in
the accuracy and efficiency of Text-to-SQL systems Xu et al. (2017); Zhong et
al. (2017); Bogin et al. (2019); Wang et al. (2018); Yu et al. (2018a);
Scholak et al. (2021); Xie et al. (2022); Wang et al. (2022a); Chen et al.
(2021); Sun et al. (2022).

Recent work has focused on fine-tuning transformers or using pre-built large
language models (e.g., ChatGPT) with prompt tuning and in-context examples.
For example, Pourreza and Rafiei (2023) explored in-context learning using
ChatGPT to generate SQL statements, and Dong et al. (2023) explored zero-shot
text-to-SQL generation using ChatGPT. Wang et al. (2020a) also proposed a
relation-aware self-attention mechanism for text-to-SQL generation, achieving
an accuracy of 65.6% on the Spider dataset when combined with BERT Wang et al.
(2020a). In another independent work, Scholak et al. (2021) introduced the
PICARD method, which uses incremental parsing for fine-tuning formal
languages. This led to state-of-the-art results on both the Spider and CoSQL
datasets. Wang et al. (2022a) introduced a novel approach to schema linking
using the Poincaré distance metric. Their results established a new benchmark
in performance, outperforming rule-based methods across multiple datasets and
showcasing the effectiveness of their probing method. A more recent thorough
analysis of the Codex language model’s text-to-SQL abilities was undertaken by
Rajkumar et al. (2022), whose findings highlighted the model’s competitive
performance across benchmarks, even without finetuning. Particularly on the
Spider benchmark, Codex achieved an accuracy of up to 67%. Their work also
indicated that using a small set of in-domain examples could boost Codex’s
performance beyond some finetuned state-of-the-art models.

Tabular Data Understanding. There has been a wide array of papers about
understanding tabular data beyond text-to-SQL Badaro et al. (2023). According
to Badaro et al. (2023), there are six common tabular data tasks: Fact-
checking, question answering, semantic parsing (i.e., text-to-SQL), table
retrieval, table metadata prediction, and table content population. Fact-
checking related work has generally focused on predicting whether a
statement/claim is factual, given the knowledge available in a Table Yang and
Zhu (2021). Table retrieval research has focused on finding a table that
contains the answer to a particular question Wang et al. (2022b, 2021). Table
metadata prediction involves predicting information about the table, such as
the column name or a relation between two columns Suhara et al. (2022); Du et
al. (2021). Finally, table content population involves filling the cells
within a table because of missing or incorrect data Iida et al. (2021); Tang
et al. (2021).

Intuitively, our task can be considered a combination of semantic parsing and
table content population. The former (semantic parsing) is the text-to-SQL
task, and the table population we are predicting is malicious or benign
information for network traffic. We can think of the malicious information as
a missing column in the database. But, more importantly, this is a highly
specialized task that large language models cannot easily reason about. Hence,
our dataset provides a unique research test bed for integrating highly
specialized knowledge into LLMs for tabular QA.

##  3 Data

Figure 2: Text-to-SQL and malicious network traffic data collection pipeline
overview.

| Train | Dev | Test
---|---|---|---
# Examples | 6591 | 2197 | 2197
Average Question Length | 2.3 | 2.3 | 2.5
Min Question Length | 5 | 6 | 6
Max Question Length | 63 | 53 | 46
Average SQL Length | 16.3 | 16.5 | 16.4
Min SQL Length | 5 | 5 | 5
Max SQL Length | 146 | 140 | 140
# Tables | 12
# Columns | 173

Table 1: Basic overview of the the text-to-SQL data.

| Train | Dev | Test
---|---|---|---
# Examples | 125,000 | 57,199 | 57,199
# Malicious Examples | 50,000 | 19,701 | 19,697
# Features | 19 | 19 | 19

Table 2: Basic overview of the network traffic data used to train and evaluate
malicious traffic.

In this section, we describe the data creation process for text-SQL pairs, the
source of the network traffic and sensor data, and how the network traffic
data was organized for training our malicious network traffic activity
detection model. As shown in Figure 2, the data curation pipeline comprises
five major steps. First, we curate the data for the database. Second, we
“annotate” text-SQL pairs. Third, we partition network traffic data from the
database to be used to train and evaluate a malicious traffic detector.
Fourth, we review the text-SQL pairs, removing incorrect, irrelevant, or
unclear queries. Moreover, we paraphrase each text-SQL pair to provide
diversity in how things are specified. Finally, we perform an additional round
of review after the paraphrase process.

###  3.1 Database Collection and Creation

We curate the data for our IoT database from two sources: IoT-23 Garcia et al.
(2021) and the Smart Building Sensor Data Hong et al. (2017).

IoT-23. The IoT-23 dataset is created to facilitate the development and
validation of intrusion detection systems (IDS) for IoT devices. It contains
benign and malicious network traffic recordings. The network traffic recorders
are stored in PCAP files and Zeek logs. For this study, we focus on the Zeek
logs. Zeek Paxson (1999), formerly known as Bro, is an open-source network
security monitoring tool. Its primary purpose is to analyze network traffic
and generate high-level logs, metrics, and events that abstract the raw data
into more meaningful and actionable insights. Zeek is widely used in network
security, monitoring, and forensic analysis. There are conn.log, dns.log,
files.log, http.log, npt.log, and weird.log. The conn.log records connection-
level information detailing the sessions seen on the network. A list of the
columns in the conn.log is found in Table 5. Each row in the conn.log is
annotated with malicious or benign and the type of malicious activity (e.g.,
DDoS, command and control, specific malware, and more). We discuss this more
in the Network Traffic subsection. dns.log contains DNS request and response
data. files.log stores details about files transferred over supported
protocols, such as HTTP or FTP. http.log captures detailed HTTP request and
response information. ntp.log contains information related to NTP
transactions, such as timestamp updates, server-client interactions, version
details, and other attributes specific to NTP communications. Finally,
weird.log logs anomalies or unusual behaviors in network traffic. Each dataset
is processed and stored as an independent table in the database.222More
details on Zeek logs can be found at docs.zeek.org/

Smart Building Sensors. The Smart Building Sensor Data is a dataset derived
from 255 sensors strategically deployed across 51 distinct rooms spanning four
floors of a university building. The dataset contains humidity, CO2,
temperature, luminosity, and motion sensor readings. Each reading is related
to a specific room in the building. This dataset presents a unique opportunity
for empirically exploring patterns associated with indoor spaces’ physical
attributes, particularly when combined with network traffic in a synthetic
building-level database. Each sensor type (humidity, luminosity, etc.) is
stored as a unique Table in our database, where each row represents a sensor
read. Intuitively, the goal is to have a comprehensive database that may be
used in a smart building setting, containing both the raw sensor information
and meta data (network traffic) for smart devices.

###  3.2 Text-to-SQL Pair Annotation

The SQL queries were created using two major approaches: programmatically
using a templated approach similar to Wang et al. (2020b) and manually
creating text-SQL pairs without templates. We describe each of these
approaches in detail below:

Templates. Following the work by Wang et al. (2020b), we generate templates
that fit two categories: retrieval queries and reasoning queries. Retrieval
queries are primarily meant to extract specific records or data from the
database. Reasoning queries are more complex and often involve several logical
operations and conditions. They often require the model to comprehend
intricate relations between different parts of the question or between
multiple database tables. The distinction is helpful because different query
types can be challenging in their ways. Retrieval queries test the model’s
ability to correctly identify and fetch data, while reasoning queries test its
ability to process and integrate multiple pieces of information.

In total, we created 27 templates containing simple and complex queries.
Templates are generated to create queries containing JOINs, HAVING statements,
aggregation operations (e.g., average), and nested queries.333Code and a
comprehensive list of all the queries will be made upon acceptance.. An
example template is

     SELECT $AGG_OP ($AGG_COLUMN)+
      FROM $TABLE WHERE ($COND_COLUMN
      $COND_OP $COND_VALUE)+

In the above expression, `$AGG_OP` represents aggregation methods (e.g.,
AVG(), MAX(), and MIN()), `$AGG_COLUMN` represents the column to perform the
aggregation on (e.g., “duration” from conn.log), `$TABLE` represents the table
the column is pulled from, `$COND_COLUMN` (e.g., orig$\\_$h representing the
IP address), `$COND` represents a conditional operator (e.g., $>$, $<$, $=$),
and `$COND_VALUE` represents the value to check (e.g., 192.168.1.1). An
example query generated from the template is

     SELECT AVG(duration)
      FROM CONN_LOG WHERE (orig_h
      = "192.168.1.1")

where items such as `$AGG_OP` are replaced with `AVG()`.

After creating the text-SQL pairs using templates, we paraphrased (reworded)
each text piece to add diversity in the ways each question type is asked. Six
researchers manually paraphrased each question. For instance, the
automatically generated sentence, “List the distinct proto for the DNS LOGs
table with TTLs equal to 2523” would be transformed into “Provide a list of
unique DNS proto values with a TTLs value of 2523”, where the sentence is now
more natural. All students had expertise in databases and were data analytics
majors. The text-SQL pairs were assigned randomly to each researcher. In
total, we create a total of 10,000 text-SQL pairs using templates.

Manual Creation. It is difficult to create templates that capture complex or
unique queries. Hence, student researchers also manually created text-SQL
pairs without using template-generated pairs. In total, 985 manually curated
pairs were collected.

SQL-Review and Dataset Statistics. After curating and paraphrasing the text-
SQL pairs, we performed a multi-round review process. Each text-SQL pair was
reviewed to measure whether the text was clear. This was done by having
different annotators review another annotator’s text-SQL pairs and paraphrases
to ensure they could create the same SQL statement. Each researcher would
create an SQL prompt, test the logic against a database, and after the query
is successfully executed, SQL questions would be generated from the tables and
variables in the Iot-23 dataset. Also, there were situations where manual
text-SQL pairs were either incorrect or unrealistic; hence, these pairs were
removed or paraphrased before incorporating them into the entire dataset.
Overall, the entire data collection process took 1.5 years. The final
annotated data statistics can be seen in Table 1. The dataset used to train
the text-to-SQL models consisted of 10,985 rows. Each row contained a SQL
query and a corresponding description, question, or prompt. The SQL queries
varied in complexity but consisted primarily of arguments such as select
distinct, max, avg, having, filtering, and join. On average, the prompts
contained sixteen words, with the shortest prompt containing five words and
the longest containing 146.

###  3.3 Network data

The network traffic data comes from the IoT-23 dataset, which is used to train
and evaluate our ability to detect malicious activity. We split the data into
train, validation, and test sets based on attack type. Each session in the
conn.log is labeled with one of ten attack-related labels: Attack, Benign,
C&C, DDoS, FileDownload, HeartBeat, Mirai, Okiru, Torii, and
PartOfAHorizontalPortScan.

Kus et al. (2022) show that training and testing on the same attack types
results in high F1 scores but performance drops for unknown attack types. To
address this, we ensure the same attack type in the training set is not in the
validation and test sets. The training set contains network traffic related to
PartOfAHorizontalPortScan and Okiru, while other attack types are used in
validation and test datasets. All malicious activities are merged into a
single “malicious” label, and IP addresses and timestamps are randomized to
prevent data leakage. The appendix provides details of the columns and
features used and a summary of the data.

##  4 Method

In this section, we describe the approach we developed to address the text-to-
SQL task and malicious traffic detection tasks jointly.

Schema for text-to-SQL. The table schema must be included with the model input
to train a model to generate SQL queries specific to our database. The schema
includes all tables and variables from our database (IoT and sensor data).
Formally, let $t_{i}$ represent a table $i$, and let $c_{i,j}$ represent a
column $j$ in table $i$. Each column has an attribute $a_{i,j}$ representing
the $j$-th column’s datatype in table $i$. For instance, we have the table
conn.log, which stores information about connections/sessions. Two columns
within conn.log include orig$\\_$h and orig$\\_$p. The attribute assigned to
the orig$\\_$h column is text since it contains strings (IP addresses). The
attribute assigned to orig$\\_$p is number (representing the port number).
Given all of the tables, columns, and attributes in a database, we generate
the schema represented in the form of
$s=[*,t_{1},c_{1,1},a_{1,1},c_{1,2},a_{1,2},t_{2},c_{2,1},a_{2,1},\ldots]$. In
practice, this looks like $s$ = [*, conn.log, orig$\\_$p, text, $\ldots$,
weird.log, orig$\\_$p, text, $\ldots$]. We concatenate the schema to each
input text before being passed to the T5 models to generate the SQL
statements.

|  | Validation | Test
---|---|---|---
|  | Execution Acc | Logical Acc | Execution Acc | Logical Acc
| Methods that can only Generate SQL Statements
Fine-tuned | BART | .693 | .233 | .400 | .232
T5-base | .904 | .729 | .827 | .746
T5-large | .966 | .868 | .928 | .861
| Methods that can detect Malicious Traffic and Generate SQL Statements
Prompt-based | GPT3.5 Few-Shot | .813 | .147 | .841 | .177
Fine-tuned + Malware MT Learning | T5-base | .927 | .837 | .956 | .851

Table 3: Text-to-SQL generation results

Input for Malicious Traffic Detection. Instead of passing the schema and text
as input for malicious traffic detection, as we do for the text-to-SQL
generation, we pass an instruction and formatted tabular data. Let $p$
represent the instruction and $t$ represent the formatted tabular data. We
concatenate both to form the input $x=[p,t]$. This work uses the instruction
“Is the following network information Malicious?”. Also, the tabular data
(row) is formatted as $t_{1}t_{2}\cdots t_{n}$, where each tabular data
column/value is represented as a string. Moreover, everything is concatenated
using space as the delimiter. In practice, this looks like “192.168.1.1 80
192.161.2.2 8080 $\cdots$.” Note that there are no spaces in the values
available in the conn.log file, which contains the network data used for
malicious traffic detection. If this work is expanded to other Zeek logs,
other delimiters would need to be explored.

Training. To train the model, we fine-tune the Flan-T5-base Chung et al.
(2022) model. The model is trained using the Adam optimizer Kingma and Ba
(2014) with a minibatch size 4 and a learning rate .0001. We trained the model
for a total of 15 epochs. The model was trained by simply combining the data
formatted as described in Sections 4.

##  5 Results

In this section, we describe the evaluation metrics, our baseline models, and
the results for text-to-SQL prediction and malicious network traffic
detection. We also provide an informative error analysis.

Evaluation Metrics. We use two primary metrics for evaluating the text-to-SQL
results: Logical Accuracy and Execution Accuracy. Logical Accuracy assesses
the correctness of the logical structure and semantics of the generated SQL
with the target SQL (i.e., measuring whether two SQL queries are exactly the
same). However, a potential pitfall of relying solely on Logical Accuracy is
that two queries may be correct but written differently. On the other hand,
Execution Accuracy evaluates the results obtained when the generated SQL is
run on a database. This metric is vital because the ultimate goal is to
extract accurate information from the database, regardless of the SQL’s
structure. However, a high Execution Accuracy doesn’t guarantee that the SQL
query is optimal or semantically correct. It’s possible for an inefficient or
technically incorrect query to yield the desired results that are returned by
the ground-truth query. Hence, we consider both Logical and Execution Accuracy
in our study. We use the standard classification metrics macro-precision,
macro-recall, and macro-F1 to evaluate our models’ malicious network traffic
detection performance.

|  |  | Validation |  |  | Test |
---|---|---|---|---|---|---|---
|  | precision | recall | F1 | precision | recall | F1
| Methods that can only detect Malicious Traffic
Baselines | Stratified | .500 | .500 | .498 | .502 | .502 | .501
Uniform | .503 | .503 | .491 | .497 | .497 | .485
Random Forest | .879 | .697 | .714 | .878 | .694 | .710
SVM | .874 | .693 | .709 | .872 | .689 | .704
Fine-tuned | T5-base | .883 | .708 | .728 | .882 | .704 | .723
T5-Large | .900 | .777 | .804 | .904 | .775 | .802
| Methods that can detect Malicious Traffic and Generate SQL Statements
Prompt-based | GPT3.5 Zero-Shot | .167 | .388 | .215 | .183 | .392 | .220
GPT3.5 Few-Shot | .741 | .761 | .711 | .671 | .640 | .543
Fine-tuned + Malware MT Learning | T5-base | .810 | .684 | .697 | .808 | .680 | .693

Table 4: Malicious traffic detection results.

Baseline models. We explore two major baselines to evaluate the performance of
detecting malicious web traffic: Support Vector Machines (SVM) and Random
Forest. The input of the models includes all of the features listed in Table 5
except for ts, uid, origh$\\_$h, resp$\\_$h, and tunnel_parents (i.e., all
unique identifiers and IP addresses are removed). The models used to create
the baseline include stratified, uniform, random forest, and support vector
machines. We also explore two random baselines: stratified and uniform. The
stratified baseline randomly predicts each class based on the class proportion
in the training dataset, and the uniform baseline randomly predicts each class
with equal probability. Finally, we evaluate transformer models Flan-T5-base
and Flan-T5-Large where the input is formatted as described in Section 3.
Finally, we evaluate using GPT3.5 with few-shot prompts (64 examples). For the
GPT3.5 model, the data is supplied in a json-like format (label, value) pairs
so it knows what each value represents.

For text-to-SQL, we explore two types of fine-tuned baselines. For the fine-
tuned models for text-to-SQL, we evaluate three models: Flan-T5-base, BART
Lewis et al. (2020), and Flan-T5-large. Each model is trained using the same
schema defined in Section 4. These models are not trained on the network
traffic data. We also evaluate GPT3.5 using in-context examples. We provide 64
in-context examples from the training dataset to make predictions. In general,
our GPT3.5 prompt follows the work of Gao et al. (2023), which achieved state-
of-the-art performance on the Spider dataset Yu et al. (2018b).

Text-to-SQL. In Table 3, we report the results on the text-to-SQL task. We
compare the baselines to models fine-tuned only on the text-to-SQL corpus and
to a model trained on the text-to-SQL and network traffic data. Overall, we
find that the larger model T5-Large outperforms the T5-base model when fine-
tuned only on text-to-SQL data. The T5-Large model achieves a logical accuracy
of .861 on the test set and an execution accuracy of .928. However, when
jointly trained on both datasets, we find that the T5-base model can nearly
match (and beat) the performance of the larger model. Specifically, the
T5-base model achieves a logical accuracy of .851 and an execution accuracy of
.956 on the test data with multi-task training, thus matching and
outperforming the T5-Large model trained only on the text-to-SQL data.

Malicious Network Traffic Detection. In Table 4, we report the results of
detecting malicious network traffic. We find that the Random Forest
outperforms other methods for the baseline models. The random forest model had
an F1 score of .710 and a recall score of .694. The SVM had similar results,
with an F1 score of .704 and a recall score of .689. Moreover, the GPT3.5
method performs poorly on the task, with only an F1 of .543 on the test step,
a light improvement over random. We hypothesize that the validation
performance is slightly better because the LLM was able to understand those
attacks better than the test set attacks. However, the transformer-based
models (T5-base and T5-large) substantially outperformed all baseline models.
The Flan-T5-Large model was the top-performing fine-tuned model model, with an
F1 score of .802 and a recall score of .775. Overall, compared to the text-to-
SQL results, we find that training on both malicious traffic detection and
text-to-SQL reduces the performance of the network traffic task. When
analyzing the results, we find that the model struggles to identify malicious
items, mostly labeling examples as Benign.

Error analysis. Why does the T5-base model match and outperform the T5-large
model when trained on both datasets? Our analysis shows that much of the
improvement is on the conn.log-related queries. The conn.log was the table
used as input when training the malicious network traffic detection-related
aspect of our model. Specifically, for logical accuracy, 142 examples in the
test dataset contained items related to the conn.log table. The T5-base model
missed 42 of them. The jointly trained T5-base model only missed 27. Some of
the errors were major, where the T5-base model did not generate a SQL
statement at all, where the T5-large model returned the correct statement
(e.g., “SELECT service FROM IoT23$\\_$CONN$\\_$LOG GROUP BY service HAVING
AVG(resp$\\_$bytes) >= 829”).

We also hypothesize that while we did not train to make inferences about other
tables in the database, by better understanding the conn.log table, the model
can better understand how it relates more to other tables via JOIN queries.
This better understanding of table relationships potentially results in
improvements for other tables as well.

##  6 Conclusion

Databases hold large amounts of structured knowledge across various sectors,
and efficient access to this data is essential. Our study was driven by the
goal of NLIDB, which is to simplify data access beyond the complexities of
SQL. While there have been advancements in text-to-SQL systems, our research
emphasizes the importance of retrieving and understanding the data. With the
introduction of the IoT-SQL dataset, we’ve provided a unique resource with the
ability to predict aspects not in the database (i.e., malicious network
activity) and generate SQL statements based on an input text query. Moreover,
the dataset contains many temporal queries that are missing or limited in
prior text-to-SQL datasets. Our findings show that models trained to query and
reason about data improve SQL generation performance.

Overall, there are two major avenues for future work. First, we plan to
explore more complex models on the dataset, particularly on more complex
training, validation, and test sets. For example, recent work suggests that
exploring different data split methods (e.g., based on SQL length, tables, or
column names) can improve the measure of generalizability Gan et al. (2022);
Tarbell et al. (2023). Second, we will explore more sophisticated methods of
detecting malicious network activity. Malicious activity may be related to
multiple sessions within the Zeek Conn.log. Developing a system that can
reason over multiple rows in the database can potentially generate substantial
improvements.

##  7 Acknowledgements

We acknowledge Dr. Glenn Dietrich, a co-author who passed away before this
paper was published. This work would not have been possible without his
support.

##  8 Limitations

Our study acknowledges several limitations that warrant discussion. Firstly,
while our novel IoT-SQL dataset provides a rich collection of text-SQL pairs
and network traffic data, the specific focus on IoT environments and network
traffic may limit the generalizability of our findings to other domains or
types of data. This specialization means that models trained on our dataset
might not perform as well when applied to databases with different structures
or content, such as healthcare or financial databases. However, it is still a
novel domain for tabular QA, which state-of-the-art LLMs (e.g., GPT3.5)
struggle to understand, thus providing a new testbed for understanding how to
add new functionality to the models. We also understand that GPT4 may perform
better than GPT3.5, but because of the size of the network data, the
experiments are expensive. GPT3.5 experiments cost nearly $600, not including
small preliminary experiments. There are also things that could have improved
the results, e.g., finding the most similar in-context examples. But, again,
the cost was prohibitive because of our limited research budget.

Also, our approach relies heavily on the quality and diversity of the SQL
queries and the paraphrased text. Despite our efforts to generate diverse and
complex queries, certain query structures or linguistic variations may still
be underrepresented. This underrepresentation could impact the model’s ability
to generalize across unseen queries or to handle nuanced variations in natural
language.

Another significant limitation lies in the multi-task learning approach for
joint training on text-to-SQL generation and malicious network traffic
detection. While this approach improved the text-to-SQL performance, it did
not enhance and, in some cases, slightly reduced the accuracy of malicious
traffic detection. This suggests a potential trade-off when balancing multiple
tasks, and further research is needed to optimize such multi-task learning
frameworks to ensure that improvements in one task do not come at the expense
of another.

In summary, while our contributions are significant, addressing these
limitations through future research will be crucial for advancing the state of
text-to-SQL systems and their application to diverse and complex datasets to
really understand all types of data beyond just generating a SQL statement.

## References

  * Acar et al. (2020) Abbas Acar, Hossein Fereidooni, Tigist Abera, Amit Kumar Sikder, Markus Miettinen, Hidayet Aksu, Mauro Conti, Ahmad-Reza Sadeghi, and Selcuk Uluagac. 2020\.  Peek-a-boo: I see your smart home activities, even encrypted!  In _Proceedings of the 13th ACM Conference on Security and Privacy in Wireless and Mobile Networks_ , pages 207–218.
  * Badaro et al. (2023) Gilbert Badaro, Mohammed Saeed, and Paolo Papotti. 2023.  Transformers for tabular data representation: A survey of models and applications.  _Transactions of the Association for Computational Linguistics_ , 11:227–249.
  * Bogin et al. (2019) Ben Bogin, Jonathan Berant, and Matt Gardner. 2019.  Representing schema structure with graph neural networks for text-to-sql parsing.  In _Proceedings of the 57th Annual Meeting of the Association for Computational Linguistics_ , pages 4560–4565.
  * Chen et al. (2021) Mark Chen, Jerry Tworek, Heewoo Jun, Qiming Yuan, Henrique Ponde de Oliveira Pinto, Jared Kaplan, Harri Edwards, Yuri Burda, Nicholas Joseph, Greg Brockman, et al. 2021.  Evaluating large language models trained on code.  _arXiv preprint arXiv:2107.03374_.
  * Chung et al. (2022) Hyung Won Chung, Le Hou, Shayne Longpre, Barret Zoph, Yi Tay, William Fedus, Eric Li, Xuezhi Wang, Mostafa Dehghani, Siddhartha Brahma, et al. 2022.  Scaling instruction-finetuned language models.  _arXiv preprint arXiv:2210.11416_.
  * Dahl et al. (1994) Deborah A Dahl, Madeleine Bates, Michael K Brown, William M Fisher, Kate Hunicke-Smith, David S Pallett, Christine Pao, Alexander Rudnicky, and Elizabeth Shriberg. 1994.  Expanding the scope of the atis task: The atis-3 corpus.  _Proceedings of the workshop on Human Language Technology_ , pages 43–48.
  * Deng et al. (2022) Xiang Deng, Huan Sun, Alyssa Lees, You Wu, and Cong Yu. 2022.  Turl: Table understanding through representation learning.  _ACM SIGMOD Record_ , 51(1):33–40.
  * Dong et al. (2023) Xuemei Dong, Chao Zhang, Yuhang Ge, Yuren Mao, Yunjun Gao, Jinshu Lin, Dongfang Lou, et al. 2023.  C3: Zero-shot text-to-sql with chatgpt.  _arXiv preprint arXiv:2307.07306_.
  * Du et al. (2021) Lun Du, Fei Gao, Xu Chen, Ran Jia, Junshan Wang, Jiang Zhang, Shi Han, and Dongmei Zhang. 2021.  Tabularnet: A neural network architecture for understanding semantic structures of tabular data.  In _Proceedings of the 27th ACM SIGKDD Conference on Knowledge Discovery & Data Mining_, pages 322–331.
  * Gan et al. (2022) Yujian Gan, Xinyun Chen, Qiuping Huang, and Matthew Purver. 2022.  Measuring and improving compositional generalization in text-to-sql via component alignment.  In _Findings of the Association for Computational Linguistics: NAACL 2022_ , pages 831–843.
  * Gao et al. (2023) Dawei Gao, Haibin Wang, Yaliang Li, Xiuyu Sun, Yichen Qian, Bolin Ding, and Jingren Zhou. 2023.  Text-to-sql empowered by large language models: A benchmark evaluation.  _arXiv preprint arXiv:2308.15363_.
  * Garcia et al. (2021) Sebastian Garcia, Agustin Parmisano, and Maria J Erquiaga. 2021.  Iot-23: A labeled dataset with malicious and benign iot network traffic. 2020.
  * Giordani and Moschitti (2013) Alessandra Giordani and Alessandro Moschitti. 2013.  Automatic generation and reranking of sql-derived answers to nl questions.  In _Trustworthy Eternal Systems via Evolving Software, Data and Knowledge: Second International Workshop, EternalS 2012, Montpellier, France, August 28, 2012, Revised Selected Papers 2_ , pages 59–76. Springer.
  * Hong et al. (2017) Dezhi Hong, Quanquan Gu, and Kamin Whitehouse. 2017.  High-dimensional time series clustering via cross-predictability.  In _Artificial Intelligence and Statistics_ , pages 642–651. PMLR.
  * Iida et al. (2021) Hiroshi Iida, Dung Thai, Varun Manjunatha, and Mohit Iyyer. 2021.  Tabbie: Pretrained representations of tabular data.  In _Proceedings of the 2021 Conference of the North American Chapter of the Association for Computational Linguistics: Human Language Technologies_ , pages 3446–3456.
  * Kingma and Ba (2014) Diederik P Kingma and Jimmy Ba. 2014.  Adam: A method for stochastic optimization.  _arXiv preprint arXiv:1412.6980_.
  * Kus et al. (2022) Dominik Kus, Eric Wagner, Jan Pennekamp, Konrad Wolsing, Ina Berenice Fink, Markus Dahlmanns, Klaus Wehrle, and Martin Henze. 2022.  A false sense of security? revisiting the state of machine learning-based industrial intrusion detection.  In _Proceedings of the 8th ACM on Cyber-Physical System Security Workshop_ , pages 73–84.
  * Lewis et al. (2020) Mike Lewis, Yinhan Liu, Naman Goyal, Marjan Ghazvininejad, Abdelrahman Mohamed, Omer Levy, Veselin Stoyanov, and Luke Zettlemoyer. 2020.  Bart: Denoising sequence-to-sequence pre-training for natural language generation, translation, and comprehension.  In _Proceedings of the 58th Annual Meeting of the Association for Computational Linguistics_ , pages 7871–7880.
  * Navid Yaghmazadeh and Dillig (2017) Isil Dillig Navid Yaghmazadeh, Yuepeng Wang and Thomas Dillig. 2017.  Sqlizer: Query synthesis from natural language.  In _International Conference on Object-Oriented Programming, Systems, Languages, and Applications, ACM_ , pages 63:1–63:26.
  * Paxson (1999) Vern Paxson. 1999.  Bro: a system for detecting network intruders in real-time.  _Computer networks_ , 31(23-24):2435–2463.
  * Popescu et al. (2003) Ana-Maria Popescu, Oren Etzioni, and Henry Kautz. 2003.  Towards a theory of natural language interfaces to databases.  In _Proceedings of the 8th international conference on Intelligent user interfaces_ , pages 149–157.
  * Pourreza and Rafiei (2023) Mohammadreza Pourreza and Davood Rafiei. 2023.  Din-sql: Decomposed in-context learning of text-to-sql with self-correction.  _arXiv preprint arXiv:2304.11015_.
  * Rajkumar et al. (2022) Nitarshan Rajkumar, Raymond Li, and Dzmitry Bahdanau. 2022.  Evaluating the text-to-sql capabilities of large language models.  _arXiv preprint arXiv:2204.00498_.
  * Scholak et al. (2021) Torsten Scholak, Nathan Schucher, and Dzmitry Bahdanau. 2021.  Picard: Parsing incrementally for constrained auto-regressive decoding from language models.  In _Proceedings of the 2021 Conference on Empirical Methods in Natural Language Processing_ , pages 9895–9901.
  * Srinivasan Iyer and Zettlemoyer (2017) Alvin Cheung Jayant Krishnamurthy Srinivasan Iyer, Ioannis Konstas and Luke Zettlemoyer. 2017.  Learning a neural semantic parser from user feedback.  In _Proceedings of the 55th Annual Meeting of the Association for Computational Linguistics (Volume 1: Long Papers)_ , pages 963–973.
  * Suhara et al. (2022) Yoshihiko Suhara, Jinfeng Li, Yuliang Li, Dan Zhang, Çağatay Demiralp, Chen Chen, and Wang-Chiew Tan. 2022.  Annotating columns with pre-trained language models.  In _Proceedings of the 2022 International Conference on Management of Data_ , pages 1493–1503.
  * Sun et al. (2022) Runxin Sun, Shizhu He, Chong Zhu, Yaohan He, Jinlong Li, Jun Zhao, and Kang Liu. 2022.  Leveraging explicit lexico-logical alignments in text-to-sql parsing.  In _Proceedings of the 60th Annual Meeting of the Association for Computational Linguistics (Volume 2: Short Papers)_ , pages 283–289.
  * Tang and Mooney (2000) Lappoon R Tang and Raymond Mooney. 2000.  Automated construction of database interfaces: Intergrating statistical and relational learning for semantic parsing.  In _2000 Joint SIGDAT Conference on Empirical Methods in Natural Language Processing and Very Large Corpora_ , pages 133–141.
  * Tang et al. (2021) Nan Tang, Ju Fan, Fangyi Li, Jianhong Tu, Xiaoyong Du, Guoliang Li, Sam Madden, and Mourad Ouzzani. 2021.  Rpt: relational pre-trained transformer is almost all you need towards democratizing data preparation.  _Proceedings of the VLDB Endowment_ , 14(8):1254–1261.
  * Tarbell et al. (2023) Richard Tarbell, Kim-Kwang Raymond Choo, Glenn Dietrich, and Anthony Rios. 2023\.  Towards understanding the generalization of medical text-to-sql models and datasets.  _arXiv e-prints_ , pages arXiv–2303.
  * Vo et al. (2022) Ngoc Phuoc An Vo, Octavian Popescu, Irene Manotas, and Vadim Sheinin. 2022.  Tackling temporal questions in natural language interface to databases.  In _Proceedings of the 2022 Conference on Empirical Methods in Natural Language Processing: Industry Track_ , pages 179–187.
  * Wang et al. (2020a) Bailin Wang, Richard Shin, Xiaodong Liu, Oleksandr Polozov, and Matthew Richardson. 2020a.  Rat-sql: Relation-aware schema encoding and linking for text-to-sql parsers.  In _Proceedings of the 58th Annual Meeting of the Association for Computational Linguistics_ , pages 7567–7578.
  * Wang et al. (2018) Chenglong Wang, Kedar Tatwawadi, Marc Brockschmidt, Po-Sen Huang, Yi Mao, Oleksandr Polozov, and Rishabh Singh. 2018.  Robust text-to-sql generation with execution-guided decoding.  _arXiv preprint arXiv:1807.03100_.
  * Wang et al. (2021) Fei Wang, Kexuan Sun, Muhao Chen, Jay Pujara, and Pedro Szekely. 2021.  Retrieving complex tables with multi-granular graph representation learning.  In _Proceedings of the 44th International ACM SIGIR Conference on Research and Development in Information Retrieval_ , pages 1472–1482.
  * Wang et al. (2022a) Lihan Wang, Bowen Qin, Binyuan Hui, Bowen Li, Min Yang, Bailin Wang, Binhua Li, Jian Sun, Fei Huang, Luo Si, et al. 2022a.  Proton: Probing schema linking information from pre-trained language models for text-to-sql parsing.  In _Proceedings of the 28th ACM SIGKDD Conference on Knowledge Discovery and Data Mining_ , pages 1889–1898.
  * Wang et al. (2020b) Ping Wang, Tian Shi, and Chandan K Reddy. 2020b.  Text-to-sql generation for question answering on electronic medical records.  In _Proceedings of The Web Conference 2020_ , pages 350–361.
  * Wang et al. (2022b) Zhiruo Wang, Zhengbao Jiang, Eric Nyberg, and Graham Neubig. 2022b.  Table retrieval may not necessitate table-specific model design.  In _Proceedings of the Workshop on Structured and Unstructured Knowledge Integration (SUKI)_ , pages 36–46.
  * Xie et al. (2022) Tianbao Xie, Chen Henry Wu, Peng Shi, Ruiqi Zhong, Torsten Scholak, Michihiro Yasunaga, Chien-Sheng Wu, Ming Zhong, Pengcheng Yin, Sida I Wang, et al. 2022\.  Unifiedskg: Unifying and multi-tasking structured knowledge grounding with text-to-text language models.  _arXiv preprint arXiv:2201.05966_.
  * Xu et al. (2017) Xiaojun Xu, Chang Liu, and Dawn Song. 2017.  Sqlnet: Generating structured queries from natural language without reinforcement learning.  _arXiv preprint arXiv:1711.04436_.
  * Yang and Zhu (2021) Xiaoyu Yang and Xiaodan Zhu. 2021.  Exploring decomposition for table-based fact verification.  In _Findings of the Association for Computational Linguistics: EMNLP 2021_ , pages 1045–1052.
  * Yu et al. (2018a) Tao Yu, Zifan Li, Zilin Zhang, Rui Zhang, and Dragomir Radev. 2018a.  Typesql: Knowledge-based type-aware neural text-to-sql generation.  In _Proceedings of the 2018 Conference of the North American Chapter of the Association for Computational Linguistics: Human Language Technologies, Volume 2 (Short Papers)_ , pages 588–594.
  * Yu et al. (2019) Tao Yu, Rui Zhang, Heyang Er, Suyi Li, Eric Xue, Bo Pang, Xi Victoria Lin, Yi Chern Tan, Tianze Shi, Zihan Li, et al. 2019.  Cosql: A conversational text-to-sql challenge towards cross-domain natural language interfaces to databases.  In _Proceedings of the 2019 Conference on Empirical Methods in Natural Language Processing and the 9th International Joint Conference on Natural Language Processing (EMNLP-IJCNLP)_ , pages 1962–1979.
  * Yu et al. (2018b) Tao Yu, Rui Zhang, Kai Yang, Michihiro Yasunaga, Dongxu Wang, Zifan Li, James Ma, Irene Li, Qingning Yao, Shanelle Roman, et al. 2018b.  Spider: A large-scale human-labeled dataset for complex and cross-domain semantic parsing and text-to-sql task.  In _Proceedings of the 2018 Conference on Empirical Methods in Natural Language Processing_ , pages 3911–3921.
  * Zelle and Mooney (1996) John M. Zelle and Raymond J. Mooney. 1996.  Learning to parse database queries using inductive logic programming.  In _Proceedings of the Thirteenth National Conference on Artificial Intelligence - Volume 2_ , pages 1050–1055.
  * Zhong et al. (2017) Victor Zhong, Caiming Xiong, and Richard Socher. 2017.  Seq2sql: Generating structured queries from natural language using reinforcement learning.  _arXiv preprint arXiv:1709.00103_.

##  Appendix A Data

IoT Data |  Description
---|---
ts |  Timestamp of the first packet
uid |  Uniqie ID of the connection
id.orig_h |  Originating endpoint’s IP address (Orig)
id.orig_p |  Originating endpoint’s TCP/UDP port (or ICMP code)
id.resp_h |  Responding endpoint’s IP address (Resp)
id.resp_p |  Responding endpoint’s TCP/UDP port (or ICMP code)
proto |  Transport layer protocol of connection
service |  Detecting application protocol, if any
duration |  Connection length
orig_bytes |  Orig payload bytes, from sequence numbers if TCP
resp_bytes |  Resp payload bytes; from sequence numbers if TCP
conn_state |  Connection state
local_orig |  is Orig in Site::local$\\_$nets?
local_resp |  is Resp in Site::local$\\_$nets?
missed_bytes |  Number of bytes missing due to connection gaps
history |  Connection state history
orig_pkts |  Number of Orig packets
orig_ip_bytes |  Number of Orig IP bytes (via IP total$\\_$length header field)
resp_pkts |  Number of Resp packets
resp_ip_bytes |  Number of Resp IP bytes (via IP total$\\_$length header field)
tunnel_parents |  if tunneled, connection UID of encapsulating parent(s)

Table 5: This table contains a description of the Zeek Connection log columns,
which are used as features when predicting malicious activity.

###  A.1 Network data

As previously discussed, the network traffic data originates from the IoT-23
dataset. A sample of the data is used to train and evaluate the performance of
our ability to detect malicious activity. We split the network traffic data
into train, validation, and test sets based on the attack type. Each session
(row) in the conn.log is labeled with one of ten attack-related labels. An
Attack label involves the infected device exploiting a vulnerable service on
another system, like brute-forcing logins. Benign connections display no
malicious intent. C&C signifies a device’s connection to a Command and Control
server, observed through periodic communications or suspicious downloads. DDoS
denotes the device’s role in overwhelming a target by sending excessive
traffic. FileDownload infers a device downloading potential threats based on
connection sizes and endpoints. HeartBeat marks periodic, minimal exchanges
with a C&C server, ensuring active monitoring. Mirai, Okiru, and Torii are
labels pointing to specific botnet attack patterns, with the latter two being
less common than Mirai. Finally, PartOfAHorizontalPortScan identifies efforts
to scan various systems on the same port for vulnerabilities.

Recent work exploring malicious network traffic detection has analyzed why
much of the reported results are greater than 99% F1 Kus et al. (2022). A
major cause for these results is the training and testing on the same attack
types. When the attack type is unknown (i.e., zero-days), performance is not
as high. Hence, we split the data into training and test/validation datasets
so that the same attack type in the training dataset is not in the validation
and test sets. The training dataset contains network traffic related to
PartOfAHorizontalPortScan and Okiru. The other sessions from the conn.log with
different attach types are used in other validation and test datasets. Next,
we merge all malicious activity into a single “malicious” label. Moreover, to
avoid potential data leakage, all IP addresses and time stamps were randomized
when training and evaluating the malicious traffic detection models. A summary
of the data used for training and evaluating the malicious network activity
models is shown in Table 2 and the columns/features are shown in Table 5.