Research system DashQL – SQL for Interactive Data Analytics André Kohn and
Thomas Neumann are with Technical University of Munich. E-mails: {andre.kohn,
<EMAIL_ADDRESS>Dominik Moritz is with the Carnegie Mellon
University. E-mail<EMAIL_ADDRESS>
# DashQL – Complete Analysis Workflows with SQL

André Kohn  Dominik Moritz  Thomas Neumann

###### Abstract

We present DashQL, a language that describes complete analysis workflows in
self-contained scripts. DashQL combines SQL, the grammar of relational
database systems, with a grammar of graphics in a grammar of analytics. It
supports preparing and visualizing arbitrarily complex SQL statements in a
single coherent language. The proximity to SQL facilitates holistic
optimizations of analysis workflows covering data input, encoding,
transformations, and visualizations. These optimizations use model and query
metadata for visualization-driven aggregation, remote predicate pushdown, and
adaptive materialization. We introduce the DashQL language as an extension of
SQL and describe the efficient and interactive processing of text-based
analysis workflows.

###### keywords:

Information visualization, systems, declarative specification

K.6.1Management of Computing and Information SystemsProject and People
ManagementLife Cycle; K.7.mThe Computing ProfessionMiscellaneousEthics DashQL
Scriptcdn://activity.dashql ⬇ INPUT site TYPE VARCHAR ( default =
’app.dashql.com’ ); FETCH a FROM HTTP ( url = urlencode(’base/{}’, main.site),
); FETCH c FROM ’s3://bucket/iso3166.csv’; LOAD activity FROM a USING PARQUET;
LOAD countries FROM c USING CSV; CREATE VIEW daily AS SELECT date_trunc(’day’,
ts) AS t, sum(a.views), c.name FROM activity a, countries c WHERE a.country =
c.code GROUP BY t, c.name ORDER BY t, c.name VISUALIZE daily USING STACKED
AREA CHART; VISUALIZE activity USING TABLE;
USERANALYSISDATAUSERINPUTFETCHLOADFETCHLOADQUERYVISUALIZEVISUALIZE Example of
a DashQL script describing a complete analysis workflow. The script loads site
activity data, resolves country names via ISO 3166 country codes, aggregates
daily page views, and visualizes the data using an area chart and a table.
DashQL statements unify user interactions, data loading, and the analysis in a
single language.

##  1 Introduction

Interactive Data Analysis has evolved as an umbrella term for diverse research
around approachable, inspirational, explanatory, and efficient data
processing. Decades of prior work in these areas have assembled a
comprehensive toolbox, guiding users on their paths towards valuable data
insights. A common principle among these tools has been the unification of
graphics and database interactions, creating a gap towards database query
languages like SQL. Pioneering systems like Polaris (Tableau), for example,
shield users from database specifics by pairing a graphic taxonomy with an own
table algebra [30]. This table algebra is lowered to SQL transparently which
abstracts from subtle differences between SQL dialects and allows supporting
various database systems through a single interface.

In the meantime, however, SQL has become the de-facto standard for data
transforms at all scales, ranging from embedded systems (e.g., DuckDB [23],
SQLite [11]) to large data warehouses (e.g., Snowflake [9], F1 [24], Procella
[6], Presto [28], Redshift [10], Azure Synapse [1], CockroachDB [31], Hive
[4]). Today’s database abstractions therefore should match SQL in its
expressivity or otherwise turn into an explicit translation layer that data
analysts might have to work around. This is particularly pronounced for
advanced SQL functionality such as nested subqueries, non-inner joins, window
aggregates or grouping sets that are often omitted as early victims during
generalization. A lack of these features can render today’s tools insufficient
for analysts that use SQL as their mental model for database interactions.

Additionally, database abstractions prevent holistic optimizations of analysis
workflows. The optimization of SQL queries is a well studied problem but is
usually unaware of how data is ingested and how the results are consumed [3].
Data analysts therefore propagate information back into the database, for
example, by optimizing requests for a following visualization. This is not
only error prone but exposes relational optimizations to the user. Database
abstractions also obfuscate capabilities of the underlying database. It is not
uncommon in today’s analysis tools to prepare and cache volatile data to
optimize the repeated and interactive query evaluation during exploration
[33]. However, this turns out to be a pitfall as it nullifies common database
optimizations like projection and selection pushdown. Structured file formats
like Parquet allow reading data partially based on the query columns and
filters. A tool that lacks these query-driven optimizations might therefore be
_slower_ if it loads _unnecessary_ data for a workflow.

We expand the vision of Wu et al. [38] and propose a language for a Data
Visualization Management System (DVMS) that embeds data retrieval, loading,
and visualization into SQL. We call this SQL dialect DashQL and explain how a
single coherent language model can drive interactive analysis workflows.
DashQL – Complete Analysis Workflows with SQL shows the first example of a
DashQL script that visualizes grouped timeseries data using an area chart and
table. In the figure, the input script on the left is translated to a graph of
tasks that drives the parallel evaluation of statements. The right side of the
figure hints at the visual output of the script as an interactive dashboard
including an input field at the top of the screen, followed by the two
visualizations.

The contribution of this paper is twofold. We first introduce the language
grammar and statement semantics in Section 2 and outline how a SQL dialect
facilitates interactive exploration, scalable dashboards, and workflow
development. We then describe the efficient evaluation of DashQL workflows in
Section 3 and present holistic optimizations that use metadata for remote
predicate pushdown and adaptive materialization. Section 3 also introduces
AM4, an optimization in DashQL that accelerates visualizations with time
series data. We demonstrate DashQL examples throughout the paper and author an
interactive analysis workflow step-by-step in Section 4. We measure the
performance of the holistic optimization AM4 in Section 5. We close with a
discussion of related work in Section 6 and a summary of the paper in Section
7.

##  2 Grammar of Analytics

DashQL unifies the predominant grammar of relational [8] database systems,
SQL, with a grammar of graphics [35] into a grammar of analytics. This section
introduces the DashQL language and its role in an analytics system. We first
list the grammar rules of DashQL and describe the semantics of every new
statement. Afterward, we present three advantages of driving analysis
workflows with a coherent analysis language.

###  2.1 SQL Extension

Figure 1: Grammar rules of the five DashQL statements SET, INPUT, FETCH, LOAD
and VISUALIZE that are combined with the statement rules of PostgreSQL.

DashQL introduces the five statements SETSET , INPUTINPUT , FETCHFETCH ,
LOADLOAD and VISUALIZEVISUALIZE to the SQL language. Together, they extend SQL
just enough to specify where data is located, how it can be loaded and how it
can be visualized for users. This allows DashQL to describe complete analysis
workflows in self-contained scripts, while preserving the expressiveness of
arbitrary SQL queries. The grammar rules of all statements are shown in Figure
1 and are outlined in the following.

SET

SET is a utility statement that defines global script properties as individual
key-value pairs. This allows modifying script evaluation settings or provide
script metadata such as titles, descriptions or versions.

INPUT

INPUT declares values that are provided to the script at runtime. For example,
an INPUT statement with identifier x and value type FILE presents an input
control to users that opens a file picker dialog when clicked. The provided
file is then exposed to the remainder of the script through the identifier x.
INPUT may further be followed by an explicit component type and configuration
options, matching additional settings like default values.

FETCH

FETCH accompanies INPUT as the second statement that declares raw data for
DashQL scripts. In its simplest form, the rule FETCH name FROM uri specifies a
raw data source as a single URI. The value "https://a/b.parquet", for example,
declares a remote file that will be loaded using HTTPS. If a simple URI is not
sufficient, the statement may alternatively be written with the explicit
keyword HTTPS followed by fine-granular settings such as the method type or
request headers. Similarly, the FETCH statement allows supporting additional
source types such as AWS S3, following the same syntax.

The fourth statement LOADLOAD defines how raw data can be loaded into the
database. We deliberately separate the fetching of opaque data and the
extraction of relations since the boundary between these two can be fuzzy and
depends on the capabilities of the underlying database. DuckDB, for example,
can partially scan remote Parquet files over HTTPS using a dedicated table
function and a virtual file system abstraction. This collapses both statements
into following SQL statements, emphasizing the decoupled nature of
declarations in a script and their efficient execution. Other databases
without these capabilities might need to execute either one or both tasks
explicitly upfront. Similar to FETCH, the LOAD statement can also be extended
with additional keywords to introduce new data formats to the language.

SCRIPT

⬇

VISUALIZE (SELECT * FROM activity , countries WHERE ...... ) USING TABLE;

REL_NAMEREL_NAMETABLE_REFTABLE_REFPROJECTIONFROM_CLAUSESELECT_STMT0011......22......ASTInteractive
ExplorationScalable DashboardsCollaborative DevelopmentWHERE country =
codeGROUP BY country)USING TABLE;USING STACKED BAR
CHART;+-+ClientClientClientUSING TABLE ;USING AREA CHART ;

Figure 2:  DashQL scripts as a driver for data analysis workflows. AST nodes
store the location in the input text, the node type, the attribute key, the
index of the parent node, and either a raw value or a span of children nodes.
Script-based analysis workflows allow for interactive exploration, scalable
dashboards, and a collaborative workflow development.

The last statement VISUALIZEVISUALIZE displays data using charts and tables.
Creating visualizations is an iterative process that benefits from short
round-trip times between ideas and their realizations. DashQL offers
approachable and fast exploration by combining a simple and short syntax with
a fallback to a full grammar of graphics for refinements. After all,
visualizations are created in tandem with SQL statements which already provide
useful information such as the attribute order of SQL projections or data
types.

For example, users might want to display timeseries data with a time attribute
t, and a value attribute v backed by a SQL query such as CREATE TABLE a AS
SELECT t, v ...... . Creating the first visualization for this table can be as
simple as VISUALIZE a USING LINE. Without further information, DashQL assumes
that t and v were deliberately provided as first and second attributes
referring to x and y values of the line chart. Alternatively, SQL column
aliases can be used in anticipation of ambiguities to name x and y explicitly,
as in SELECT v AS y, t AS x ...... , . Later iterations may then add a third
attribute in the SQL projection list followed by VISUALIZE a USING MULTI LINE,
in which case the new attribute would map to the line color. This example
shows that the interplay with traditional SQL statements may provide enough
information to simplify the syntax significantly through carefully chosen
defaults.

This simplified syntax enables rapid prototyping but may not suffice for
advanced analysis reports. The grammar therefore supports an alternative
grammar rule with VISUALIZE a USING ( ...... ) that describes visualizations
using raw Vega-Lite specifications. In fact, the _simple_ form is lowered down
to Vega-Lite internally, enabling automatic rewrites as explicit specification
when refinements are needed. In alignment with SQL, the Vega-Lite attributes
are case insensitive in DashQL and JSON objects are replaced with nested key-
value pair lists enclosed by round brackets. The systematic generation and
completion of Vega-Lite specifications is described in Section 3.4.

###  2.2 Driving Analysis Workflows

An extended SQL dialect offers an opportunity to describe entire analysis
workflows in self-contained scripts. These scripts become the single source of
truth for an analysis system and can drive features such as interactive
exploration, scalable dashboards, and collaborative workflow development.
Figure 2 illustrates these features based on a single visualization statement
that displays the results of a join as a table.

####  2.2.1 Interactive Exploration

DashQL demystifies system internals by replacing a multitude of configuration
knobs with guided textual editing. DashQL strikes a balance between
flexibility and intuition by providing short and long versions of the
different grammar rules. This flexibility allows users to start the
exploration with short statements and later refine the workflow by manually
adjusting inferred properties. This simplifies the exploration as the
syntactical differences between statements stay small.

The example in Figure 2 expresses the intent to display data as a table by
writing VISUALIZE ...... USING TABLE. The short syntax allows altering the
visualization quickly. For example, a user can replace the keyword TABLE with
AREA CHART to change the visualization type and add the keyword STACKED to
group areas based on an additional attribute. Once the correct chart type is
found, a user can adjust fine-granular configuration options such as colors
and labels through explicit Vega-Lite settings. These rewrites can either be
done manually by changing the script text or by modifying a previously
rendered chart. The result is an interactive loop where partially evaluated
DashQL workflows guide users through following refinements.

Additionally, the analysis workflows are interactive themselves through the
INPUT statement. These statements parameterize worklows explicitly by exposing
variables to viewers. This allows embedding arbitrary complex SQL queries into
the workflow and steer them through input controls. A popular alternative to
INPUT statements is to derive raw SQL text from input values through text
interpolation. This is flexible, but complicates the semantic analysis of
workflows as it gives up crucial information about statement dependencies,
types, and the exact usage of parameters. It also requires a preprocessing
step to generate the actual script text which does not align well with the
continuous and iterative re-evaluation of workflows.

DashQL distinguishes between the analysts authoring workflows and the viewer
that consume the workflows’s output. Viewers are not exposed to the language
but instead only see the results from statements as opaque analysis
dashboards. Authors, in contrast, see the language and visual output side-by-
side and benefit from semantic information in the script editor. Interactive
exploration in DashQL is therefore emphasized differently for these two user
groups as authors benefit from frictionless feedback loops for textual changes
while viewers require efficient re-evaluations after changing input values.

####  2.2.2 Scalable Dashboards

DashQL simplifies the sharing of analysis dashboards. A workflow is a single
self-contained script text and can be treated as such for the distribution to
multiple users. This decouples the workflow description from the evaluating
analysis tool, similarly to SQL being the common denominator between
relational database systems. Sharing a data analysis workflow is cheap since
there is no dependency on specific service resources except for the workflow’s
input data. The price for serving this data is often lower than maintaining
computing resources for traditional server-based analytics tools, more so with
scalable cloud storage services and large content delivery networks.

We introduce the language DashQL alongside a reference implementation that is
powered by DuckDB-Wasm, an efficient WebAssembly version of the analytical
database DuckDB [23] for the web. It evaluates entire analytical workflows ad-
hoc in the browser, presenting a cost-efficient and interactive solution
without dedicated analytics servers. The lack of a dedicated server increases
the horizontal scalability of the system at the cost of higher bandwidth
requirements for the viewers. According to Vogelsgesang et al, shared analysis
workflows on smaller datasets are not uncommon today [34]. They state, that
only approximately 600 out of 62 thousand workbooks uploaded to the service
Tableau Public contain more than a million tuples. All other workflows fall
into the range of browser-manageable data sizes, eliminating the need for
dedicated computing resources in the cloud.

DashQL also supports workflows that process larger datasets but reduce the
data size quickly based on user input. For example, if a workflow processes
event data of a logging service, the entire dataset for all users might easily
exceed petabytes of records. But if the workflow itself analyzes events of a
specific user over a fixed period, the datasets can get sufficiently small.
Section 3.6 introduces holistic optimizations that optimize the amount of
loaded data based on SQL queries in the workflow. Yet, the language DashQL is
not limited to small datasets. It instead offers an opportunity to dynamically
combine client and server-side implementations to optimize for scalability and
interactivity wherever possible and fall back to traditional server-side
processing when needed.

Previous Script012345

⬇

INPUT duration TYPE INTERVAL;

FETCH data FROM "s3://bucket/file1";

LOAD activity FROM data USING PARQUET;

CREATE VIEW grouped AS

SELECT date_trunc( ’day’ , ts) AS time, sum(hits), site

FROM activity WHERE ts > (now() - main.duration)

GROUP BY time, site ORDER BY time, site;

VISUALIZE grouped USING TABLE;

VISUALIZE grouped USING STACKED BAR CHART;

Next Script01234

⬇

INPUT duration TYPE INTERVAL;

FETCH data FROM "s3://bucket/file1";

LOAD activity FROM data USING PARQUET;

CREATE VIEW grouped AS

SELECT date_trunc( ’hour’ , ts) AS time, sum(hits), site

FROM activity WHERE ts > (now() - main.duration)

GROUP BY time, site ORDER BY time, site;

VISUALIZE grouped USING STACKED BAR CHART;

INPUTFETCHLOADQUERYVISUALIZEVISUALIZEVISUALIZEKEEPKEEPKEEPUPDATEDELETEKEEPPrevious
ASTNext
ASTINPUTFETCHLOADQUERYVISUALIZEVISUALIZEINPUTLOADFETCHQUERYVISUALIZEDROP
TABLEDROP VISDROP VIS$+$$\Rightarrow$012345KEEPKEEPKEEPUPDATEDELETEKEEP

Figure 3:  Example of a task graph that is derived from a previous task graph
and an AST-based script difference. The two scripts visualize grouped
timeseries data and differ in a deleted statement and the grouping
granularity. The AST colors equal statements in greengreen , changes in
blueblue and deletions in orangeorange .

####  2.2.3 Collaborative Development

DashQL also simplifies collaborative development of analysis workflows. Text-
based version control systems like _Git_ dominate the distributed software
development today. Since DashQL workflows are self-contained scripts, they can
be developed as part of a versioned development process. Users can fork DashQL
workflows and contribute changes back through simple textual updates. This
process is facilitated by the concise grammar of SQL that keeps the textual
differences small. Figure 2 demonstrates this versioning by adding a grouping
clause and a changed chart type in the example statement. The patch tracks the
new grouping by the country attribute as well as the visualization as stacked
bar chart in the same script. DashQL workflows can therefore be created,
updated, forked and discussed in environments that have already proven their
effectiveness in collaborative development.

##  3 Implementation

In this section, we outline the implementation of a DashQL powered analysis
tool. We first describe the efficient AST encoding that we use as textual
language model for analysis workflows. This model allows the runtime to update
only the parts of the execution state that have changed instead of full re-
evaluations. We then introduce the concept of tasks and show how adaptive task
graphs can be maintained using fast difference computations. We discuss the
extensibility of DashQL and the use of query metadata to simplify declarative
visualizations for fast exploration. And finally, we present two examples of
holistic optimizations that are accelerating the coupled workflow components.

###  3.1 AST Format

DashQL translates many user interactions into modifications of the associated
script text. This positions the underlying text model as fundamental component
of the entire system. Our implementation is therefore built around a fast
syntactical analysis, backed by an efficient representation of the abstract
syntax tree (AST). The parser extends the SQL grammar rules of PostgreSQL and
allocates compact AST nodes into a single, bump-allocated memory buffer. This
accelerates parsing and increases the cache efficiency of any following
operations such as tree traversals. An AST node is exactly
$20\text{\,}\mathrm{B}$ large and stores the location in the input text, the
node type, the attribute key and the index of the parent node. It also stores
either a raw integer value or a span of children nodes in the same buffer. The
text location associates each node with the substring matched by its grammar
rule, enabling partial rewrites of individual statements. The AST further acts
as an auxiliary data structure and references string literals in the original
script text instead of copying them. Children of an AST node are further
stored in sorted order based on the attribute key, accelerating key lookups
and recursive comparisons.

Figure 2 illustrates the AST encoding of an example statement that visualizes
an inline SQL query joining two base relations. The AST presents two nodes of
type REL_NAME that match the table names A and B. Nodes are created by the
parser following a post-order traversal of matched grammar rules which emits
children before their parents. This eliminates additional serialization steps
since nodes can be written to the buffer while parsing. In the example, the
relation names are matched as table references and then form the two children
of a from clause. The output of the syntactic analysis is a program
description representing statements as offsets of root nodes in this AST
buffer. This representation is not only cache efficient, but also simplifies
the crossing of system boundaries as the consecutive memory buffer and fixed
size nodes simplify the communication between system components and languages.

###  3.2 From AST to Task

The actionable units of our system are called tasks. Tasks are derived from
statements and form a graph based on the statement dependencies. A query
statement that references a load statement, for example, translates into a
query task that scans the output of a load task. These tasks are partially
ordered and evaluate the entire script starting with data ingestion and ending
with the visualization of derived tables. New tasks are derived on every user
interaction based on the difference between the AST and its predecessor. This
includes tasks to undo the effects of deleted statements, update the effects
of modified statements and add the effects of new statements.

For example, if a SQL statement that created a table is deleted from the
script, the system derives a task to undo the effects by dropping the table.
This mechanism is more abstract than the traditional transaction isolation of
database systems as all tasks are maintaining a single workflow state that is
updated with respect to changes in the script text and the user input.
VISUALIZE statements, for example, compile Vega-Lite specifications only once
and delete or update the specification only when the statement changes. FETCH
statements that download data using HTTP will further cache the data until a
script change invalidates the output. The task graph drives the execution of
an analysis workflow and serves as anchor for any operations on derived state.

###  3.3 Adaptive Task Graphs

The task graph of DashQL is adaptive as it reflects all continuous changes in
the script and the user input. We implement a variant of an algorithm known as
_Patience Diff_ that is implemented in the version control systems _GNU
Bazaar_ and _Git_. The algorithm derives task updates from the difference
between two scripts and works as follows:

We first determine all unique statement mappings between a script and its
predecessor. Two statements are compared based on their ASTs instead of texts
for whitespace insensitivity and support for incremental changes. The
similarity can be quantified by counting equal AST nodes in two simultaneous
DFS traversals and weighting them by the distance to the AST root. The tree
traversals profit from the compact and cache efficient encoding of nodes into
a single AST buffer. Next, we compute the longest common subequence among the
mapped statements and use them as anchors for the remaining assignments. The
remainder is then iterated in sequence and assigned to the most similar
matches that haven’t been assigned yet. This identifies new and deleted
statements and emits a similarity score for the rest.

Afterward, we determine the _applicability_ of all previous tasks. A task is
_applicable_ if it was derived from a statement that stayed the same, does not
_transitively_ depend on an _inapplicable_ task, and is not followed by an
_inapplicable_ task that successfully modified the own output. The
_applicability_ can be determined through a single DFS traversal with
backwards propagation when encountering an _inapplicable_ task. _Applicable_
tasks and their state are migrated and marked as completed while the effects
of all other tasks are updated or undone.

Figure 3 illustrates the entire process with an example of two scripts that
analyze site activity data stored in AWS S3. The first script starts with an
INPUT statement that receives a time interval for the analysis. It then
downloads the data from an AWS S3 bucket using a FETCH statement and inserts
the data into the database as Parquet file using LOAD. The statements are
followed by a traditional SQL query to filter the site activity in the input
interval and compute aggregates grouped by days. The final two statements
visualize the result of this query as a table and a stacked bar chart. The
second script is almost identical to the first one except that the data is now
grouped per hour instead of days and is no longer visualized as table.

AST buffers of both scripts are shown below with the node color indicating the
statement differences. The first three statements and the last are equal and
therefore don’t need to change. The query statement differs in the string
literal that is passed to the function date_trunc and is marked as updated.
The first visualization statement is no longer present in the new script and
is marked as deleted.

The figure also contains a task graph derived for the previous script. It
shows one task for every statement and a checkmark indicating that all of them
were successfully executed. This task graph is then combined with the computed
statement mappings to derive a new set of tasks reflecting the changes between
the scripts. The table visualization was deleted, emitting a task called DROP
VIZ to remove the table. The query statement was updated and results in the
task DROP TABLE to undo the effects of the SQL query. However, this effect
propagates since both visualizations depend on the table data. We therefore
also undo the effects of the second visualization and recreate it after
executing the updated SQL statement. The remaining tasks that fetch and load
the remote data into the database and receive the input from the user are
migrated and marked as completed.

This example demonstrates differences with the traditional script execution in
relational database systems. DashQL is defining entire analysis workflows,
including external data, visualizations, and interactions with a user. Scripts
are therefore not evaluated independently but in the context of a preceding
execution, rewarding awareness of existing state.

###  3.4 Complementing Vega-Lite

timeDATETIME15.10 00:0015.10 00:0015.10 01:0015.10 01:00...22.10 23:0022.10
23:0023.10 00:0023.10
00:00hitsBIGINT1296276638441205...2671202238754178siteVARCHARapp.dashql.comwww.dashql.comapp.dashql.comwww.dashql.com...app.dashql.comgithub/dashqlapp.dashql.comwww.dashql.com

⬇

VISUALIZE activity USING MULTI LINE CHART;

VISUALIZE activity USING (

mark = ’line’ ,

encoding = (

x = (

field = ’time’ ,

type = ’temporal’ ,

scale = (

domain = [

’15.10 00:00’ ,

’23.10 00:00’

]

)),

y = (

field = ’hits’ ,

type = ’quantitative’ ,

scale = (domain = [1205, 4178] )

),

color = (

field = ’site’ ,

type = ’nominal’ ,

scale = (

domain = [

’https://github.com/dashql’ ,

’https://app.dashql.com’ ,

’https://www.dashql.com’

]

))));

Figure 4:  Two VISUALIZE statements that produce the same time series line
chart, showing website hits of multiple websites. DashQL generates Vega-Lite
specifications based on the table schema and statistics.

Vega-Lite offers a grammar to describe an expressive range of charts in
declarative JSON specifications. The VISUALIZE statement of DashQL supports
Vega-Lite specifications as nested key-value pair lists in SQL. VISUALIZE does
not need to embed its own grammar of graphics and users already familiar with
Vega-Lite don’t have to learn a new language.

Vega-Lite specifications are self-contained and describe visualizations
without the context of an existing data model. In DashQL, visualizations are
always backed by SQL queries which offer an opportunity to auto-complete parts
of a specification. This reduces the pressure on Vega-Lite and pushes costly
data introspection into the database system. An example for this are encoding
types and scale domains. We know the data types of all involved attributes
based on the SQL metadata which enables robust defaults, for example, when
selecting between _quantitative_ , _ordinal_ , and _nominal_ encoding types.
Additionally, we can determine a value domain or range efficiently upfront
using SQL queries.

DashQL further provides simplified VISUALIZE statements that can be written in
tandem with SQL queries. This follows the observation, that explicit defaults
can guide the writing of SQL queries with respect to a subsequent
visualization. For example, users can express a preferred field assignment
through attribute aliases. A projection like SELECT time AS x, hits AS y, site
AS color ...... implictly provides the fields for a visualization that can be
reduced to VISUALIZE ...... USING MULTI LINE CHART. These mappings can still
be overruled by explicit settings but enable short statement variants for fast
exploration. Even without matching aliases, users can fall back to listing
attributes in a certain order. By default, DashQL assigns the first three
attributes to the encoding channels x, y, color, resulting in the same output.

Figure 4 lists two VISUALIZE statements. The first one presents a simplified
syntax that instructs DashQL to draw a chart with multiple lines. The second
one describes the same output using an embedded Vega-Lite specification.
Internally, DashQL uses the table on the right to derive the specification of
the second statement for the first one. The table has the three columns time,
hits and site with the data types DATETIME, BIGINT and VARCHAR. By default, a
chart with multiple lines requires encoding declarations for x-values,
y-values and the line color. Without further hints, DashQL assigns the columns
in-order, using the time attribute for x, the number of hits for y, and the
site name as color. The encoding types are derived based on the encoded column
and the column data types. An x-encoding backed by a DATETIME attribute is
_temporal_ , by default. The other encoding types are _quantitative_ for
y-values of type BIGINT and _nominal_ for color-values of type VARCHAR. DashQL
then resolves the domain for each of the scales in the encodings. The domains
of temporal and quantitative scales are computed using minimum and maximum
aggregates and yield the interval between the 15th and 23nd of August for the
time attribute and the value range between 1205 and 4178 for the hit count.
The domain of the nominal scale is then resolved by querying distinct site
values, emitting the three websites of the DashQL project.

In summary, Vega-Lite provides a robust grammar for declarative visualizations
in DashQL. We further extend the capabilities of Vega-Lite by completing
specifications based on the contextual query metadata. This accelerates the
data exploration without losing the flexibility of a full specification
whenever it is needed.

###  3.5 Language Extensions

cityVARCHAROklahoma
CityTulsaNormannpopINTEGER681054413066128026countyVARCHAROklahoma CountyTulsa
CountyCleveland CountypopINTEGER796292669279295528JSON

⬇

FETCH d FROM ’https://api’;

LOAD cities FROM d USING JSON (

jmespath = ’{

city: keys(@.cities),

pop<EMAIL_ADDRESS>
}’

);

LOAD counties FROM d USING JSON (

jmespath =<EMAIL_ADDRESS>
county: @.key, pop: @.value

}’,

);

{

"cities": {

"Oklahoma City": 681054,

"Tulsa": 413066,

"Normann": 128026

},

"counties": [

{ "key": "Oklahoma County", "value": 796292 },

{ "key": "Tulsa County", "value": 669279 },

{ "key": "Cleveland County", "value": 295528 }

]

}

Figure 5:  Two load statements that extract two relations from a single JSON
document using JMESPath expressions. Both expressions extract populations in
Oklahoma. The first expression emits the city data in column-major format, the
second expression returns county data in row-major format.

The syntax of the DashQL statements INPUT, FETCH, LOAD and VISUALIZE end with
optional settings provided as key-value pair lists. This offers a mechanism to
extend DashQL without modifying the grammar rules or the language model. The
settings translate to a generic dictionary that is passed to the derived
tasks. Custom task implementations can read this dictionary and enable
extensions based on available keys.

Our reference implementation, for example, extends the loading of JSON data
through JMESPath expressions. By default, our embedded database DuckDB-Wasm
can load a table from a JSON document in two formats. Either in row-major
format as top-level array of objects where each object contains all attributes
of the relation or in column-major format as top-level object with members
storing column arrays. If a JSON document is not in either of those formats,
it has to be transformed first. The JSON task therefore checks for the key
”jmespath” in the settings. If it is present, the task evaluates the
expression on the input data first before loading it into the database.

Figure 5 lists an example DashQL script, that loads two relations from a
single JSON document that was returned from a remote HTTP API. The documents
stores population data of Oklahoma. City populations are stored as a single
object with city names as properties, whereas county populations are provided
as an array of objects. The first expression emits an object with the field
_city_ storing an array of city names and the field _pop_ holding an array of
population values. The second expression returns the county object array with
changed attribute names. This example demonstrates the extensibility of the
DashQL language through custom task implementations that can be configured
through dynamic configuration options.

###  3.6 Holistic Optimization

Data transformations can be expensive which makes their optimization
indespensable for every data analysis workflow. Query optimizers are therefore
a vital component of every data processing system today and have a significant
impact on overall execution times. Research around query optimization is
profound and has been expanded for decades. Yet, databases are universal and
face the difficult task to accelerate specific queries without losing the
generality. As a result, database systems rarely include external information
during planning, leaving these non-trival problems to the applications. DashQL
unifies the data retrieval, transformations, and visualizations in the same
language which presents an opportunity for holistic optimizations.

⬇

WITH m4 AS (

SELECT round($width * (x - $lb) / ($ub - $lb)) as k,

min(x) AS min_x , max(x) AS max_x ,

min(y) AS min_y , max(y) AS max_y

FROM $user_data GROUP BY k)

SELECT x, y FROM m4, $user_data

WHERE k = round($width * (x - $lb) / ($ub - $lb))

AND (y = min_y OR y = max_y OR

x = min_x OR x = max_x)

Figure 6:  M4, a query for value-preserving time series aggregation, described
by Jugel et al [15]. This version uses a CTE instead of a subquery with equal
semantics.

0510152005101520$\Rightarrow$min(x)max(x)max(y)min(y)

⬇

SELECT min(x), arg_min(y, x) , max(x), arg_max(y, x) ,

min(y), arg_min(x, y) , max(y), arg_max(x, y) ,

round($width * (x - $lb) / ($ub - $lb)) AS bin,

FROM ( $user_data ) GROUP BY bin

Figure 7: AM4, a more efficient version of M4 that provides value-preserving
time series aggregation using a single scan and the aggregation functions
arg_min and arg_max.

####  3.6.1 Visualization-Driven Aggregation

The first example for holistic optimization is the automatic aggregation of
SQL results for VISUALIZE statements. Jugel et al introduced the value-
preserving aggregation M4 [15] to accelerate the visualization of time series
data. M4 follows the observation, that the amount of rendered data points in
line charts can be limited by the number of visible pixels on the screen.
Instead of visualizing every single tuple of a time series, we can select a
subset of the tuples based on the chart dimensions. The authors group values
by time bins and compute the four name-giving aggregates min(x), max(x),
min(y), and max(y) per bin. The associated points span a bounding box around
all tuples in a bin that intersects any pixels that should be colored for the
line chart. With DashQL, introducing M4 becomes an optimization that
propagates the visualization context towards the backing SQL query.

12341 Data Input2 Aggregate Views3 Filter Website4 Polish Aestheticsrewrite

⬇

FETCH data FROM "https://static.dashql.com/data/examples/infovis.parquet"

LOAD activity FROM data USING PARQUET;

VISUALIZE activity USING TABLE;

INPUT website TYPE VARCHAR;

CREATE TABLE activity_grouped AS

SELECT date_trunc(’hour’, timestamp) AS hour, sum(views) AS views

WHERE (website IS NULL OR website = main.website)

FROM activity GROUP BY hour;

VISUALIZE activity_grouped USING AREA CHART;

VISUALIZE activity_grouped USING (

title = ’Website Views’,

mark = (type = ’area’, line = true, opacity = 0.5),

encoding = (x = (field = ’hour’, type = ’temporal’, "title" = ’Time’,
axis.tick_count = 5),

y = (field = ’views’, type = ’quantitative’, "title" = ’Views’)));

Figure 8:  Authoring an example analysis workflow with DashQL. The workflow
explores website activity data in four steps. The steps are labeled with 1 to
4 and associate textual changes in the script with adjusted visual output.
Visualization statements are colored in greengreen , the input statement and
the corresponding predicate in orangeorange .

M4 is a value-preserving time series aggregation equivalent to the one listed
in Figure 6. The query scans the relation user_data and computes the four
aggregates grouped by a bin key. Afterward, the query resolves the
corresponding x\- and y-values of the aggregates by joining the aggregates
again with the input data. A tuple in the input qualifies in that join, if
there exists an aggregate with the same key and either x or y equals an
extreme value. The query does not rely on any specific aggregation functions
which makes it compatible with a wide variety of database systems.

Yet, the original version of M4 introduces a subtle but important assumption.
It scans the input relation twice and joins the extreme values to reconstruct
the corresponding input tuples. This assumes, that the extreme values are
unique as the join might otherwise emit duplicates. For example, a constant
function like $f(x)=42$ will resolve 42 as minimum and maximum y value of
every group. The following join will then emit the entire input relation since
all tuples contain the same value for y. To support non-unique y-values, we
therefore also have to make the output distinct on k, x, and y. M4 therefore
consists of a repeated scan, a join and two aggregations or otherwise has to
fall back to significantly slower window functions.

We propose an alternative version of M4, called AM4, shown in Figure 7. It
uses the aggregation functions arg_min and arg_max, sometimes implemented as
min_by, and max_by, that are provided by several databases today (e.g., by
ClickHouse, DuckDB and Presto). The function arg_min(a, b) selects an
arbitrary attribute for a where b is minimal and can be computed alongside a
min(b) aggregate at negiligble costs. We extend M4 by additionally computing
the aggregates arg_min(y, x)arg_min(y, x) , arg_max(y, x)arg_max(y, x) ,
arg_min(x, y)arg_min(x, y) and arg_max(x, y)arg_max(x, y) . This resolves
existing points associated with the extreme values in a single efficient
grouping, eliminating the second scan and the distinct aggregation.

####  3.6.2 Adaptive Materialization

A second example for holistic optimization is called Adaptive Materialization.
DashQL statements like FETCH and LOAD only _declare_ data sources and formats.
It is left the optimizer to decide at runtime if the file contents should be
materialized as table upfront or if the data should be loaded lazily as part
of a following SQL query. This decision not only depends on a single query but
the entire script context as multiple statements might refer to the same data.
If the file format allows it, DashQL can further use projection and predicate
pushdown of databases to only fetch relevant parts of a file based on the
specific query.

Predicate pushdown is a common optimization technique in databases and
describes the evaluation of predicates as far down in the query plan as
possible. The direction _down_ refers to the widespread representation of
relational algebra where relations form _leaves_ of a tree that are combined
using joins. When optimizing relational algebra, a common task is to push
individual predicates towards these _leaves_ to reduce the cardinality of a
relation as early as possible. If such a predicate is evaluated right after
scanning file formats like Parquet, the database can evaluate the predicates
on file statistics and skip reading entire row groups.

The database DuckDB, for example, supports reading remote Parquet files
partially using a HTTP filesystem and skips row groups based on predicates in
the table function parquet_scan. With DuckDB, DashQL fetches and loads the
Parquet files in following SQL queries, if the data is not consumed by
multiple statements. Formats like CSV, on the other hand, require downloading
and parsing the entire file, independent of subsequent filters. In these
cases, DashQL materializes the CSV contents once and shares the table with all
following statements. The decision to materialize data therefore depends on
the data source, the data format, all queries in the script and the
capabilities of the underlying database. We call this technique Adaptive
Materialization and see it as an opportunity to replace traditional caching
logic with query-driven optimization passes.

##  4 Example Data Exploration

We demonstrate data exploration with DashQL by constructing an example
analysis workflow. The example analyzes a dataset with website activity data
and builds a dashboard to view daily total page views for individual websites.
We describe the textual changes to the script in every step and how they
affect the reevaluation of the derived task graph. The script text and the
associated output of the tool are shown in Figure 8.

Data Input. Our exploration begins with a declaration of the workflow’s input
data. The first script is labeled with 1 and consists of three DashQL
statements. A FETCH statement declares that a file with name data can be
retrieved using HTTP, a LOAD statement interprets this data as Parquet file
and a VISUALIZE statement colored in greengreen displays the file contents.
The figure also presents the output of the first statements that visualizes
the unaggregated site activity data using a single table. This table is
virtualized, which means that only visible rows are rendered. In SQL, this
virtualization translates to LIMIT and OFFSET clauses to only query the
relevant subset of the data. With a coherent language model, we can propagate
the LIMIT and OFFSET specifiers towards the data retrieval during an
optimization pass. As a result, this first step only reads the file metadata
and the first bytes of the Parquet file using HTTP range requests. When the
user scrolls through the data, the table dynamically reads following tuples by
adjusting both specifiers. The internal WebAssembly database also uses an
accelerating readahead buffer for the remote file to minimize the number of
roundtrips to the remote server. This reduces the latency that users have to
wait until seeing a visualization and provides a graceful fallback to large
reads when the data is being requested.

Aggregate Views. Next, we want to aggregate the site activity to inspect the
hourly sum of page views. We modify the script as shown in 2 and add an
explicit SQL statement that groups the site activity data as well as an
additional VISUALIZE statement in greengreen to display the aggregates using
an area chart. During reevaluation, the former workflow state is left
untouched since the previous statements were neither modified, nor
invalidated. The new query statement, however, needs to scan the attributes
timestamp and views of all tuples in the Parquet file to compute the new
aggregates. The additional visualization statement waits for the grouping to
complete and then displays an area chart. This demonstrates the generation of
Vega-Lite specifications as outlined in Section 3.4 since the tool
automatically selects the time and sum attributes for the x- and y-values and
identifies temporal and quantitive axes.

Filter Website. The next step makes the analysis dashboard interactive.
Instead of showing the total page views across all websites, we want to filter
the activity data by a website name that is provided dynamically by the user.
For this, 3 introduces an INPUT statement colored in orangeorange and includes
a filter predicate in the SQL statement. The new input with name website is of
type VARCHAR and displays a text field on top of the previous area chart. The
added filter predicate checks if the website is either NULL or if the website
attribute of the tuple equals the website variable in the script. By default,
the input value will be NULL which means that the dashboard will show the
total page views until a website name is entered. During reevaluation, the
Patience Diff algorithm identifies the additional WHERE clause in the query
statement and marks it as updated. The system therefore drops and recreates
the grouped activity table as well as the area chart that consumes its data.
The query now filters the attribute website, which means that an additional
column needs to be fetched from the remote Parquet file. This input statement
shows the capability of DashQL to parameterize any SQL statement without
explicit text instantiation. The AST allows us to reference the input variable
by qualifying its name with the default schema.

Polish Aesthetics. The last step polishes the aesthetics of the generated
analysis dashboard. The short syntax of DashQL offers a frictionless
visualization of arbitrary SQL statements but may be insufficiently generic
for a final workflow output. For example, the former area chart visualization
falls back to the SQL attribute names for axis labels and default colors for
the covered area. As described in Section 3.4, DashQL internally lowers the
short syntax to verbose specifications. To adjust fine-granular settings,
DashQL can therefore rewrite existing statements and specify all lowered
options explicitly. 4 demonstrates this by replacing the single area chart
visualization with explicit settings after interacting with the previously
rendered chart. It uses the verbose specification to adjust the title, the
axis labels, the tick count and the area opacity in the workflow script.

This example demonstrates that DashQL allows for a progressive construction of
analysis workflows. The interplay between textual adjustments and continuous
visualizations provides short feedback loops during the data exploration.
Propagating limit and offset specifiers is an example for a holistic
optimization that reduces the amount of loaded data based on user input.

##  5 Visualization with AM4

Figure 9:  Downloading and rendering dominate the visualization times for
increasing data sizes in a client-server setting. M4 and AM4 efficiently
reduce large datasets to a small cardinality that can be visualized quickly.

In this section, we measure the performance of AM4, a visualization-driven
aggregation and an example for a holistic optimization in DashQL. As described
in Section 3.6, AM4 accelerates chart rendering and reduces the total amount
of downloaded data in a client-server setting by filtering minimum and maximum
values of grouped data. We want to demonstrate the effects of this
optimization by analyzing render and download times with increasing data
sizes. The experiments were performed on a Ryzen 5800X CPU with Node.js
v17.6.0 that is powered by the V8 engine v9.6.

Figure 9 contains three plots. The plot at the top shows in greengreen color
the time it takes to draw all points on a Cairo-backed canvas, using a
prepared Vega view. It further adds the time to download the data in blueblue
with either mobile (Cellular) or fixed broadband speeds. The Cisco Annual
Internet Report [7] projects average global network performances of
$110.4\text{\,}\mathrm{Mbit}\text{\,}{\mathrm{s}}^{-1}$ for fixed broadband
and $43.9\text{\,}\mathrm{Mbit}\text{\,}{\mathrm{s}}^{-1}$ for mobile networks
by 2023. We assume a small record size of $16\text{\,}\mathrm{B}$ and compute
the required time to download all tuples without any network latencies. Both
durations present a significant delay especially for data sizes beyond 100k
tuples that hamper any interactive exploration. The plot in the bottom right
shows execution times of M4 and AM4. If we assume a canvas width of 1000
pixels and a device pixel ratio of 2, M4 and AM4 reduce the data cardinality
to 8k tuples. AM4 computes the aggregates for a relation with 500k entries in
$22.3\text{\,}\mathrm{ms}$ and is twice as fast as M4 which takes
$53.7\text{\,}\mathrm{ms}$. The plot in the bottom left shows the render and
download and times for up to 10k tuples. A vertical line marks the resulting
8k tuples emitted by both algorithms that can be visualized quickly.

The experiment shows that both, M4 and AM4, accelerate the visualization of
large data sets. This holds even when computing the analysis locally without
downloads since rendering alone becomes expensive with an increasing number of
tuples. AM4 is therefore a good example for an optimization that propagates
data from visualizations, such as the canvas width, back to the SQL query.

##  6 Related Work

DashQL builds on ideas from declarative visualization and analysis languages
and automatically optimizes workflows to make them more scalable.

###  6.1 Declarative Analysis Languages

Visualization and analysis languages are fundamental to exploratory analysis,
either as a programming interface or as the underlying representation of a UI
tool.

Declarative, textual languages provide a high-level notation to describe data
science workflows. They often come with runtimes that optimize the data
representation and query execution. These advantages make them a popular
choice over imperative languages like Python, R, or JavaScript. For example,
SQL remains a popular tool for data scientists to express queries to databases
decades after its invention [5]. Additionally, many declarative visualization
and analysis languages have emerged. Vega [27] and Vega-Lite [26], for
example, describe visualizations in JSON syntax. Their runtimes reduce
redundant computation in these specifications and fill in rendering details.
DashQL extends this research around declarative visualizations by integrating
Vega-Lite specifications in the VISUALIZE statements. Vega also supports
declarative data loading and transformations but authoring and debugging them
can be cumbersome [14] and is often not performant enough. Dedicated analysis
languages can fill this gap, for example by extracting analytical queries into
explicit steps that can be annotated and tracked. Glinda [29] is a declarative
format for specifying data science workflows including data loading,
transformation, machine learning, and visualization. In contrast to Vega and
Vega-Lite, Glinda describes analysis steps in YAML. DashQL follows the
principles of declarative analysis languages but extends the language SQL
instead. This approaches the goal of a coherent analysis language from the
opposite direction as data ingestion and visualization are embedded into the
database query language itself.

Vega, Glinda and DashQL, as most analysis languages, build on relational
algebra and share a similar expressiveness in terms of the analyses they can
describe [8]. Beyond the analysis steps, DashQL specifications describe inputs
via UI widgets and outputs via tables and visualizations. Unlike Precision
interfaces [42] and the recent PI2 [32] which implicitly generate UIs from SQL
queries, the UI components in DashQL are explicitly described using the
statements INPUT and VISUALIZE. Like Vega visualizations, DashQL dashboards
are interactive and update reactively to changes. Vega proposed a reactive
runtime for visualizations [27] but all declarative components need to be
specified by the author. When a declaration changes, the runtime needs to re-
parse and re-evalute the entire JSON specification.

When languages are used as model in a UI tool, analysts interactively modify
an underlying specification that the system can reason about [12]. Polaris,
which led to the creation of Tableau, explored this concept with the language
VisQL [30]. In Voyager [36], people interactively change CompassQL [37]
specifications and a recommender system suggests a gallery of visualizations.
Lyra is an interactive visualization design environment that authors Vega-Lite
specifications on behalf of the user [25] . We show in the paper that DashQL
extends these ideas with an compact AST representation that allows for
efficient updates.

Systems often blend code and graphical interfaces and allow modifications
through either direct manipulation or code. Mage [16] and B2 [39] blend the
boundaries between code and UI in Jupyter Notebooks. In Sketch-n-Sketch [13],
people can write a program to generate graphics or manipulate the graphics
directly in the rendering canvas. Inspired by these ideas, DashQL scripts
describe visualizations like inputs, tables, and charts with text, but users
can also change the statement by interacting with the UI. For example, DashQL
offers to expand the short syntax of VISUALIZE statements or updates chart
dimensions in the text when resizing the UI widget.

###  6.2 Scalable Visual Analysis

Even small latencies in visual analysis systems negatively affect people’s
behavior during data exploration [18, 41]. Intial exposure to delays impair
the subsequent performance even when delays are removed. Therefore, we want
DashQL to respond to user interactions with low latency. DashQL builds on two
ideas to achieve this goal.

First, DashQL uses an efficient in-browser analytical database based on DuckDB
[23]. The database allows to evaluate analysis workflows entirely on the
client, avoiding costly roundtrips to a backend server. This lays the
foundation for a distributed evaluation of workflows in the future that
optimize dynamic client server scaling using a cost model [20]. Second, DashQL
leverages the declarative format of analysis scripts to apply known
optimizations from the database literature. These optimizations reduce
redundant and unnecessary computations and avoid loading data that is not
needed to answer a query. For example, DashQL reads data dynamically from
remote files based on query predicates and projected attributes [2].
Propagating such information across statements shares similarities with
provenance-supported interactions described by Psallidas et al. [22]. DashQL
further implements a variant of the algorithm M4 [15] to reduce the rendering
overhead with time series data.

Ideally, these optimizations happen transparently without the user having to
manually specify them (as they would need to if they wrote their analysis in
e.g., D3). Previous systems [19, 17, 21] used specialized engines to enable
interactive response times. DashQL does not yet apply some of the indexing
techniques these systems proposed but it supports a wide range of analysis
scenarios through general SQL queries. VegaPlus [40] is a related project that
aims to improve performance of general visualizations by extracting data
transformations from Vega [27] specifications and running them in a system
that is more scalable than the Vega runtime. VegaPlus shifts computation but
does not automatically apply data reduction techniques like M4.

##  7 Discussion

This paper introduces the language DashQL. We list example scripts throughout
the sections and discuss iterative data exploration in Section 4. The examples
demonstrate the proximity of the language to SQL and the capability to
describe complete analysis workflows. DashQL extends SQL by defining how data
can be resolved and how results should be visualized. The coherent language
model facilitates holistic optimizations covering data input, transforms and
visualizations. Nevertheless, we identify two major areas for future
improvements.

First, DashQL models interactivity with the dedicated input statement. These
statements render form controls in the resulting dashboard and reevaluate
parts of the task graph upon user interaction. This enables parameterizing
arbitrary SQL statements but raises the question how to support interactions
with rendered visualizations. A prominent example is cross-filtering where
user interactions with one visualization translate into applied filters for
another. A possible solution could be to expose the concept of Vega-Lite
signals to the language, for instance, by updating values for input statements
through brushes in a time series chart. Future versions of DashQL should
therefore focus on integrating input variables as more than just constant
scalar values in SQL queries.

Second, the language DashQL does not specify yet how a workflow can be
executed across multiple machines. Traditionally, there has been a clear
separation between the analytics server performing computations and the client
visualizing the results. With DashQL, these boundaries are blurred as a system
could spread the execution of a workflow across multiple machines. The
distributed evaluation of a workflow becomes an optimization problem that has
to consider the data locality, the bandwith and computation capacities and
resulting interaction latency. For example, large datasets might require to
evaluate certain predicates close to the data in the cloud but might still
favor analyzing the filtered results locally. We see DashQL as a step towards
distributed analysis workflows that optimize for low interaction latencies
even on large data sizes.

###### Acknowledgements.

This project has received funding from the European Research Council (ERC)
under the European Union’s Horizon 2020 research and innovation programme
(grant agreement No 725286).

## References

  * [1] J. Aguilar-Saborit, R. Ramakrishnan, K. Srinivasan, K. Bocksrocker, I. Alagiannis, M. Sankara, M. Shafiei, J. Blakeley, G. Dasarathy, S. Dash, et al. Polaris: the distributed sql engine in azure synapse.  PVLDB, 2020. doi: 10 . 14778/3415478 . 3415545
  * [2] L. Battle, R. Chang, and M. Stonebraker.  Dynamic prefetching of data tiles for interactive visualization.  In SIGMOD, pp. 1363–1375. ACM, 2016. doi: 10 . 1145/2882903 . 2882919
  * [3] L. Battle and C. Scheidegger.  A structured review of data management technology for interactive visualization and analysis.  IEEE transactions on visualization and computer graphics, 2020. doi: 10 . 1109/tvcg . 2020 . 3028891
  * [4] J. Camacho-Rodríguez, A. Chauhan, A. Gates, E. Koifman, O. O’Malley, V. Garg, Z. Haindrich, S. Shelukhin, P. Jayachandran, S. Seth, D. Jaiswal, S. Bouguerra, N. Bangarwa, S. Hariappan, A. Agarwal, J. Dere, D. Dai, T. Nair, N. Dembla, G. Vijayaraghavan, and G. Hagleitner.  Apache hive: From mapreduce to enterprise-grade big data warehousing.  In SIGMOD, 2019. doi: 10 . 1145/3299869 . 3314045
  * [5] D. D. Chamberlin and R. F. Boyce.  Sequel: A structured english query language.  In Proceedings of the 1974 ACM SIGFIDET (now SIGMOD) workshop on Data description, access and control, pp. 249–264, 1974. doi: 10 . 1145/800296 . 811515
  * [6] B. Chattopadhyay, P. Dutta, W. Liu, O. Tinn, A. McCormick, A. Mokashi, P. Harvey, H. Gonzalez, D. Lomax, S. Mittal, R. A. Ebenstein, N. Mikhaylin, H. ching Lee, X. Zhao, G. Xu, L. A. Perez, F. Shahmohammadi, T. Bui, N. McKay, V. Lychagina, and B. Elliott.  Procella: Unifying serving and analytical data at youtube.  PVLDB, 2019.
  * [7] Cisco.  Cisco annual internet report (2018–2023).  2020\.
  * [8] E. F. Codd.  A relational model of data for large shared data banks.  In Software pioneers, pp. 263–294. Springer, 2002. doi: 10 . 1007/978-3-642-59412-0_16
  * [9] B. Dageville, T. Cruanes, M. Zukowski, V. Antonov, A. Avanes, J. Bock, J. Claybaugh, D. Engovatov, M. Hentschel, J. Huang, A. W. Lee, A. Motivala, A. Q. Munir, S. Pelley, P. Povinec, G. Rahn, S. Triantafyllis, and P. Unterbrunner.  The snowflake elastic data warehouse.  In SIGMOD, 2016. doi: 10 . 1145/2882903 . 2903741
  * [10] A. Gupta, D. Agarwal, D. Tan, J. Kulesza, R. Pathak, S. Stefani, and V. Srinivasan.  Amazon redshift and the case for simpler data warehouses.  In SIGMOD, 2015. doi: 10 . 1145/2723372 . 2742795
  * [11] S. Haldar.  Inside sqlite.  O’Reilly Media, Inc., 2007.
  * [12] J. Heer, J. M. Hellerstein, and S. Kandel.  Predictive interaction for data transformation.  In CIDR, 2015.
  * [13] B. Hempel, J. Lubin, and R. Chugh.  Sketch-n-sketch: Output-directed programming for SVG.  In UIST, pp. 281–292. ACM, 2019. doi: 10 . 1145/3332165 . 3347925
  * [14] J. Hoffswell, A. Satyanarayan, and J. Heer.  Visual debugging techniques for reactive data visualization.  In Computer Graphics Forum, pp. 271–280. Wiley Online Library, 2016. doi: 10 . 1111/cgf . 12903
  * [15] U. Jugel, Z. Jerzak, G. Hackenbroich, and V. Markl.  M4: A visualization-oriented time series data aggregation.  PVLDB, pp. 797–808, 2014. doi: 10 . 14778/2732951 . 2732953
  * [16] M. B. Kery, D. Ren, F. Hohman, D. Moritz, K. Wongsuphasawat, and K. Patel.  mage: Fluid moves between code and graphical work in computational notebooks.  In UIST, pp. 140–151. ACM, 2020. doi: 10 . 1145/3379337 . 3415842
  * [17] L. D. Lins, J. T. Klosowski, and C. E. Scheidegger.  Nanocubes for real-time exploration of spatiotemporal datasets.  IEEE Transactions on Visualization and Computer Graphics, pp. 2456–2465, 2013. doi: 10 . 1109/tvcg . 2013 . 179
  * [18] Z. Liu and J. Heer.  The effects of interactive latency on exploratory visual analysis.  IEEE transactions on visualization and computer graphics, 2014. doi: 10 . 1109/tvcg . 2014 . 2346452
  * [19] Z. Liu, B. Jiang, and J. Heer.  immens: Real-time visual querying of big data.  EuroVis, 2013. doi: 10 . 1111/cgf . 12129
  * [20] D. Moritz, J. Heer, and B. Howe.  Dynamic client-server optimization for scalable interactive visualization on the web.  In DSIA, 2015.
  * [21] D. Moritz, B. Howe, and J. Heer.  Falcon: Balancing interactive latency and resolution sensitivity for scalable linked visualizations.  In CHI. ACM, 2019. doi: 10 . 1145/3290605 . 3300924
  * [22] F. Psallidas and E. Wu.  Provenance for interactive visualizations.  In HILDA, 2018. doi: 10 . 1145/3209900 . 3209904
  * [23] M. Raasveldt and H. Mühleisen.  Duckdb: An embeddable analytical database.  In SIGMOD, 2019. doi: 10 . 1145/3299869 . 3320212
  * [24] B. Samwel, J. Cieslewicz, B. Handy, J. Govig, P. Venetis, C. Yang, K. Peters, J. Shute, D. Tenedorio, H. Apte, F. Weigel, D. Wilhite, J. Yang, J. Xu, J. Li, Z. Yuan, C. Chasseur, Q. Zeng, I. Rae, A. Biyani, A. Harn, Y. Xia, A. Gubichev, A. El-Helw, O. Erling, Z. Yan, M. Yang, Y. Wei, T. Do, C. Zheng, G. Graefe, S. Sardashti, A. M. Aly, D. Agrawal, A. Gupta, and S. Venkataraman.  F1 query: Declarative querying at scale.  PVLDB, 2018. doi: 10 . 14778/3229863 . 3229871
  * [25] A. Satyanarayan and J. Heer.  Lyra: An interactive visualization design environment.  In Computer Graphics Forum, pp. 351–360. Wiley Online Library, 2014. doi: 10 . 1111/cgf . 12391
  * [26] A. Satyanarayan, D. Moritz, K. Wongsuphasawat, and J. Heer.  Vega-lite: A grammar of interactive graphics.  IEEE transactions on visualization and computer graphics, pp. 341–350, 2016. doi: 10 . 1109/tvcg . 2016 . 2599030
  * [27] A. Satyanarayan, R. Russell, J. Hoffswell, and J. Heer.  Reactive vega: A streaming dataflow architecture for declarative interactive visualization.  IEEE transactions on visualization and computer graphics, pp. 659–668, 2015. doi: 10 . 1109/tvcg . 2015 . 2467091
  * [28] R. Sethi, M. Traverso, D. Sundstrom, D. Phillips, W. Xie, Y. Sun, N. Yegitbasi, H. Jin, E. Hwang, N. Shingte, and C. Berner.  Presto: Sql on everything.  In ICDE, 2019. doi: 10 . 1109/icde . 2019 . 00196
  * [29] J. Shen, A. L. Varbanescu, H. Sips, M. Arntzen, and D. G. Simons.  Glinda: A framework for accelerating imbalanced applications on heterogeneous platforms.  In Proceedings of the ACM International Conference on Computing Frontiers. ACM, 2013. doi: 10 . 1145/2482767 . 2482785
  * [30] C. Stolte, D. Tang, and P. Hanrahan.  Polaris: A system for query, analysis, and visualization of multidimensional databases.  Communications of the ACM, p. 75–84, 2008. doi: 10 . 1145/1400214 . 1400234
  * [31] R. Taft, I. Sharif, A. Matei, N. VanBenschoten, J. Lewis, T. Grieger, K. Niemi, A. Woods, A. Birzin, R. Poss, P. Bardea, A. Ranade, B. Darnell, B. Gruneir, J. Jaffray, L. Zhang, and P. Mattis.  Cockroachdb: The resilient geo-distributed sql database.  In SIGMOD, 2020. doi: 10 . 1145/3318464 . 3386134
  * [32] J. Tao, Y. Chen, and E. Wu.  Demonstration of PI2: interactive visualization interface generation for SQL analysis in notebook.  CoRR, 2022.
  * [33] P. Terlecki, F. Xu, M. Shaw, V. Kim, and R. Wesley.  On improving user response times in tableau.  In SIGMOD, 2015. doi: 10 . 1145/2723372 . 2742799
  * [34] A. Vogelsgesang, M. Haubenschild, J. Finis, A. Kemper, V. Leis, T. Muehlbauer, T. Neumann, and M. Then.  Get real: How benchmarks fail to represent the real world.  In DBTest, 2018. doi: 10 . 1145/3209950 . 3209952
  * [35] L. Wilkinson.  The grammar of graphics.  In Handbook of computational statistics, pp. 375–414. Springer, 2012.
  * [36] K. Wongsuphasawat, D. Moritz, A. Anand, J. Mackinlay, B. Howe, and J. Heer.  Voyager: Exploratory analysis via faceted browsing of visualization recommendations.  IEEE transactions on visualization and computer graphics, pp. 649–658, 2015. doi: 10 . 1109/tvcg . 2015 . 2467191
  * [37] K. Wongsuphasawat, D. Moritz, A. Anand, J. Mackinlay, B. Howe, and J. Heer.  Towards a general-purpose query language for visualization recommendation.  In Proceedings of the Workshop on Human-In-the-Loop Data Analytics, pp. 1–6, 2016. doi: 10 . 1145/2939502 . 2939506
  * [38] E. Wu, F. Psallidas, Z. Miao, H. Zhang, and L. Rettig.  Combining design and performance in a data visualization management system.  In CIDR, 2017.
  * [39] Y. Wu, J. M. Hellerstein, and A. Satyanarayan.  B2: Bridging code and interactive visualization in computational notebooks.  In Proceedings of the 33rd Annual ACM Symposium on User Interface Software and Technology, pp. 152–165, 2020. doi: 10 . 1145/3379337 . 3415851
  * [40] J. Yang, H. K. Joo, S. S. Yerramreddy, S. Li, D. Moritz, and L. Battle.  Demonstration of vegaplus: Optimizing declarative visualization languages.  SIGMOD, 2022.
  * [41] E. Zgraggen, A. Galakatos, A. Crotty, J.-D. Fekete, and T. Kraska.  How progressive visualizations affect exploratory analysis.  IEEE transactions on visualization and computer graphics, pp. 1977–1987, 2016. doi: 10 . 1109/tvcg . 2016 . 2607714
  * [42] H. Zhang, V. Raj, T. Sellam, and E. Wu.  Precision interfaces for different modalities.  In SIGMOD, 2018. doi: 10 . 1145/3183713 . 3193570


#  Observation of harmonic lasing in the Angstrom regime at European XFEL

E.A. Schneidmiller<EMAIL_ADDRESS>F. Brinker  W. Decking  L.
Froehlich  M. Guetg  D. Noelle  M. Scholz  M.V. Yurkov  I. Zagorodnov
Deutsches Elektronen-Synchrotron (DESY), Hamburg, Germany  G. Geloni  N.
Gerasimova  J. Gruenert  J. Laksman  J. Liu  S. Karabekyan  N. Kujala  Th.
Maltezopoulos  I. Petrov  L. Samoylova  S. Serkez  H. Sinn  F. Wolff-Fabris
European XFEL GmbH, Schenefeld, Germany

###### Abstract

Harmonic lasing provides an opportunity to extend the photon energy range of
existing and planned X-ray FEL user facilities. Contrary to nonlinear harmonic
generation, harmonic lasing can generate a much more intense, stable, and
narrow-band FEL beam. Another interesting application is Harmonic Lasing Self-
Seeding (HLSS) that allows to improve the longitudinal coherence and spectral
power of a Self-Amplified Spontaneous Emission (SASE) FEL. This concept was
tested at FLASH in the range of 4.5 - 15 nm and at PAL XFEL at 1 nm. In this
paper we present recent results from the European XFEL where we successfully
demonstrated harmonic lasing at 5.9 Angstrom and 2.8 Angstrom. In the latter
case we obtained both 3rd and 5th harmonic lasing and, for the first time,
operated a harmonic lasing cascade (5th-3rd-1st harmonics of the undulator).
These results pave the way for reaching very high photon energies, up to 100
keV.

††preprint: DESY 21-036

Published 19 March 2021 in Phys. Rev. Accel. Beams 24, 030701

DOI:
https://journals.aps.org/prab/abstract/10.1103/PhysRevAccelBeams.24.030701

Copyright American Physical Society

##  I INTRODUCTION

Successful operation of X-ray free electron lasers (FELs) down to the Angstrom
regime opens up new horizons for photon science. Even shorter wavelengths are
requested by the scientific community. A possible way to extend the photon
energy range of high-gain X-ray FELs is to use harmonic lasing, which is the
FEL instability at an odd harmonic of the planar undulator murphy ; hg-2 ;
kim-1 ; mcneil ; sy-harm  developing independently from the lasing at the
fundamental. Another option, proposed in sy-harm , is the possibility to
improve spectral brightness of an X-ray FEL by the combined lasing on a
harmonic in the first part of the undulator (with an increased undulator
parameter K) and on the fundamental in the second part of the undulator. Later
this concept was named Harmonic Lasing Self-Seeded FEL (HLSS FEL) hlss .

Harmonic lasing was originally proposed for FEL oscillators colson  and was
tested experimentally in infrared and visible wavelength ranges. It was,
however, not demonstrated in high-gain FELs and at a short wavelength until
the successful experiments prab-hlss-fl2  at the second branch of the soft
X-ray FEL user facility FLASH flash ; fl2-njp  where the HLSS FEL operated in
the wavelength range between 4.5 nm and 15 nm. Later, the same operation mode
was tested at PAL XFEL at 1 nm pal-hlss . In this paper we report on recent
results from the European XFEL winni-xfel , where we successfully demonstrated
operation of HLSS scheme at 5.9 Angstrom and at 2.8 Angstrom. Moreover, we
observed for the first time the fifth harmonic lasing in a high-gain FEL and
operation of a harmonic lasing cascade.

##  II HARMONIC LASING

Harmonic lasing in single-pass high-gain FELs murphy ; hg-2 ; kim-1 ; mcneil ;
sy-harm  is the amplification process, in a planar undulator, of higher odd
harmonics developing independently of each other (and of the fundamental) in
the exponential gain regime. The most attractive feature of the saturated
harmonic lasing is that the spectral brightness (or brilliance) of harmonics
is comparable to that of the fundamental sy-harm . Indeed, a good estimate for
the saturation efficiency is $\lambda_{\mathrm{w}}/(hL_{\mathrm{sat},h})$,
where $\lambda_{\mathrm{w}}$ is the undulator period, $h$ is the harmonic
number, and $L_{\mathrm{sat},h}$ is the saturation length of the h-th
harmonic. At the same time, the relative rms bandwidth has the same scaling.
So, the reduction of power is compensated by the bandwidth reduction and the
spectral power remains the same.

Although known theoretically for a long time murphy ; hg-2 ; kim-1 ; mcneil ,
harmonic lasing in high-gain FELs was not considered for practical
applications in X-ray FELs. The situation was changed after publication of
Ref. sy-harm  where it was concluded that the harmonic lasing in X-ray FELs is
much more robust than usually thought, and can be effectively used in the
existing and future X-ray FELs. In particular, the European XFEL can greatly
outperform the specifications euro-xfel-tdr  in terms of the highest possible
photon energy: it can reach 60-100 keV range for the third harmonic lasing. It
was also shown cw  that one can keep sub-Angstrom range of operation of the
European XFEL after CW upgrade of the accelerator, even with a reduction of
the electron energy from 17.5 GeV to 7 GeV. Another application of harmonic
lasing is a possible upgrade of FLASH with the aim to increase the photon
energy up to 1 keV fl-harm-las .

###  II.1 Harmonic Lasing Self-Seeded FEL

The poor longitudinal coherence of SASE FELs stimulated theoretical and
experimental efforts for its improvement. Since external seeding seems to be
difficult to realize in the X-ray regime, a so called self-seeding scheme has
been proposed ss-soft ; ss-wake . There are alternative approaches for
reducing bandwidth and increasing spectral brightness of X-ray FELs without
using optical elements. One of them was proposed in sy-harm , and is based on
the combined lasing on a harmonic in the first part of the undulator (with
increased undulator parameter K) and on the fundamental in the second part. In
this way the second part of the undulator is seeded by a narrow-band signal,
generated via harmonic lasing in the first part. This concept was named HLSS
FEL hlss . Note that a similar concept, called pSASE, was independently
proposed in psase .

Figure 1: Conceptual scheme of a SASE FEL (top), a Harmonic Lasing Self-Seeded
FEL (middle) and a Harmonic Lasing Cascade (bottom). Green arrows correspond
to the wavelength of interest $\lambda$, amplified through the whole
undulator. Red and yellow arrows correspond to the fundamental wavelengths of
the corresponding parts of the undulator, tuned to the subharmonic of
$\lambda$.

Typically, gap-tunable undulators are planned to be used in X-ray FEL
facilities. If the maximal undulator parameter $K$ is sufficiently large, the
concept of harmonic lasing self-seeded FEL can be applied (see Fig. 1b). An
undulator is divided into two parts by setting two different undulator
parameters such that the first part is tuned to a $h$-th sub-harmonic of the
second part, which is tuned to a wavelength of interest $\lambda$. Harmonic
lasing occurs in the exponential gain regime in the first part of the
undulator. The fundamental with wavelength $\lambda\times h$ in the first part
stays well below saturation. In the second part of the undulator, the
fundamental is resonant to the wavelength previously amplified as the
harmonic. The amplification process proceeds in the fundamental, up to
saturation. In this case the bandwidth is defined by the harmonic lasing but
the saturation power is still as high as in the reference case of lasing on
the fundamental in the whole undulator, i.e., the spectral brightness
increases.

The enhancement factor of the coherence length (or bandwidth reduction factor)
that one obtains in HLSS FEL in comparison with a reference case of lasing in
the SASE FEL mode in the whole undulator, is given by hlss :

$R\simeq h\
\frac{\sqrt{L_{\mathrm{w}}^{(1)}L_{\mathrm{sat},h}}}{L_{\mathrm{sat},1}}$ (1)

Here $h$ is the harmonic number, $L_{\mathrm{sat},1}$ is the saturation length
in the reference case of the fundamental lasing with the lower K-value,
$L_{\mathrm{w}}^{(1)}$ is the length of the first part of the undulator, and
$L_{\mathrm{sat},h}$ is the saturation length of harmonic lasing.

Despite the bandwidth reduction factor (1) being significantly smaller than
that of self-seeding schemes using optical elements ss-soft ; ss-wake , the
HLSS FEL scheme is very simple, robust, and it does not require any additional
installations, i.e., it can always be used in existing or planned gap-tunable
undulators with a sufficiently large range of accessible K-values.

###  II.2 Harmonic Lasing Cascade

Harmonic Lasing Cascade (see Fig. 1c) is a conceptually simple generalization
of HLSS towards increasing the number of undulator parts tuned to different
subharmonics of the desirable frequency. This frequency is amplified through
the whole chain being, for example, the fifth harmonic in the first part, the
third harmonic in the second part, and the fundamental in the third part of
the undulator.

The scheme similar to that shown in Fig. 1c can be applied when one installs a
shorter period undulator behind the main undulator. For example, an
installation of a short-period superconducting undulator after one of the two
hard x-ray undulators of the European XFEL is being discussed sara . The
period of this linearly polarized device is chosen to be 15 mm, and the peak
value of K is supposed to be 2.3 for the gap of 5 mm. For the electron energy
of 17.5 GeV, the undulator may operated in the range 54 - 100 keV, In this
case the main undulator can be divided into two parts with 5th and 3rd
harmonic lasing, while the saturation is achieved at the fundamental
wavelength of the superconducting undulator. For example, in 100 keV case, the
first part of the main undulator is tuned to 20 keV, while the second part to
33 keV. Then 100 keV is amplified initially as the 5th harmonic, then as the
third, and finally as the fundamental.

The reason why we do not consider operation of the main undulator only on the
fifth, or only on the third harmonic, is that one needs to keep the
fundamental of that undulator well below saturation to avoid nonlinear
harmonic generation. This can be done, in principle, with phase shifters
mcneil  (for example, one can use $2\pi/3$ and $4\pi/3$ phase shifts in case
of the 3rd harmonic). However, as it was shown in sy-harm ; fl-harm-las , this
way of suppression is not very efficient in SASE case. Thus, dividing the
undulator into two parts (with two different fundamental frequencies) makes
the suppression task much easier cw ; penn .

##  III EXPERIMENTS AT THE EUROPEAN XFEL

The European XFEL is an X-ray FEL user facility, based on a superconducting
accelerator winni-xfel . It provides ultimately bright photon beams for user
experiments since 2017. Two hard X-ray undulators (SASE1 and SASE2) and one
soft X-ray undulator (SASE3) are presently in operation. Harmonic lasing
experiments were performed in the SASE3 undulator in April and in August 2019,
and then continued in February 2020.

The electron beam energy in all cases was 14 GeV and the bunch charge was 250
pC. The SASE3 undulator consists of twenty one undulator segments, each 5 m
long, and intersections containing quadrupoles and phase shifters between the
segments und . The undulator has a period of 6.8 cm and the K-values can be
changed between 4 and 9 in the specified working range. Note that in order to
observe HLSS effect with the 5th harmonic we had to work outside of this
standard range (K was set to 3.2).

###  III.1 Third harmonic lasing at 2.1 keV

Figure 2: Scan of the K-value of the first part of the undulator consisting of
five undulator segments, tuned to 700 eV. Mean pulse energy and its standard
deviation are measured after the second part of the undulator tuned to 2.1
keV. FEL operates in ”5+7 segments HLSS” mode.

In April, 2019 we aimed at the 3rd harmonic lasing at 2.1 keV. As a
preparation step, we defined the number of undulator segments to be tuned to
the third subharmonic of 2.1 keV radiation that was our target photon energy.
We set the undulator K value to 8.74 and lased at 700 eV on the fundamental.
In the first part of the undulator we have to avoid the nonlinear harmonic
generation which means that we have to stay three or more orders of magnitude
below saturation. We have found that five undulator segments satisfy this
condition. Note that one could increase this length if one would use phase
shifters to suppress the fundamental mcneil . This, however, was not done
during the experiments described in this paper due to the lack of time and a
limited efficiency of this method sy-harm ; fl-harm-las .

Figure 3: Synchronous scan of phase shifters after first four undulator
segments. FEL pulse energy is measured after the second part of the undulator
tuned to 2.1 keV. Mean pulse energy and its standard deviation are plotted
versus a delay measured in units of the fundamental wavelength in the first
part (tuned to 700 eV). FEL operates in ”5+6 segments HLSS” mode.

Then we set the undulator K-value to 4.91 and had a normal SASE operation at
2.1 keV (wavelength 5.9 Angstrom). We chose twelve undulator segments to stay
at the onset of saturation. The pulse energy was 700 $\mu$J as measured by
using an X-ray gas monitor (XGM) xgm-1 ; xgm-2 . In the same way as it was
done in the earlier experiments at FLASH prab-hlss-fl2 , we closed the gaps of
the first five undulator segments one by one such that the undulator K became
8.74, and the resonance photon energy was 700 eV (wavelength 17.7 Angstrom).
In other words, we moved to the configuration, shown on Fig. 1, with $h=3$,
five segments in the first part of the undulator and seven segments in the
second part. As soon as K in each of the first five segments deviated from the
original value of 4.91, we observed a strong reduction of pulse energy,
measured with an XGM. However, when K approached the value of 8.74, the pulse
energy almost recovered.

Figure 4: Single-shot spectra for SASE (left column) and HLSS (right column).
Plots show spectral intensity (in arbitrary units) versus photon energy (in
eV). HLSS operates in ”5+7 segments” configuration. One can see a reduction of
number of spikes in the HLSS spectra.

We performed a scan of the K-value of the first part of the undulator, tuned
to 700 eV, and measured pulse energy after the second part, tuned to 2.1 keV.
The result is presented in Fig. 2 where one can see a sharp resonance. Taking
into account the above mentioned fact that lasing on the fundamental at 700 eV
was too weak to produce nonlinear harmonics, we can conclude that we have the
3rd harmonic lasing in the first part of the undulator at 2.1 keV that
continues as lasing on the fundamental in the second part.

An independent confirmation of harmonic lasing is the result of a scan of
phase shifters in the first part of the undulator, presented in Fig. 3. For
this scan, we used six undulator segments in the second part in order to
increase sensitivity of the system by reducing saturation effects. We
performed synchronous scans of phase shifters between the first five undulator
segments, over a range in which the fundamental radiation at $\lambda=17.7$
Angstrom slips over the electron beam by a single cycle (note that absolute
value of phase in Fig. 3 is not calibrated, i.e. zero on the plot does not
correspond to zero phase shift). If we seeded the second part of the undulator
by the nonlinear harmonic generation in the first part, the phase shifter scan
would result in a single cycle. However, one can clearly see three cycles in
Fig. 3 which proves that we had harmonic lasing.

The purpose of the HLSS scheme is to increase FEL coherence time and to reduce
bandwidth. From Equation (1) we estimate the coherence enhancement factor
$R\simeq 2$ for our experimental conditions. An increase of the coherence time
results in a corresponding reduction of the number of longitudinal modes with
respect to the SASE case. In the frequency domain we should then observe a
reduction of the average number of spikes in single-shot spectra. In an ideal
case, we could also expect a reduction of the average bandwidth by a factor of
two, to the level of $0.1\%$ FWHM. Unfortunately, a reduction of the average
bandwidth was not possible in our case, since the bandwidth is strongly
affected by the energy chirp of the electron bunches.

Figure 5: Scan of the K-value of the first part of the undulator consisting of
five undulator segments, tuned to 900 eV. Mean pulse energy and its standard
deviation are measured after the second part of the undulator tuned to 4.5
keV. FEL operates in ”5+11 segments HLSS” mode.

To measure spectra, the SASE3 monochromator spectrometer  was used in
spectrometer mode: the spectral distribution of the soft X-rays was converted
into visible light in a YAG:Ce crystal located in the focal plane of the
monochromator and was recorded by a CCD phot-diag . To resolve spikes in
single shot spectra, we reordered the spectra in the third diffraction order
with a 50 l/mm grating.

We took a series of spectra for the HLSS configuration (five segments at 700
eV and seven segments at 2.1 keV) as well as for standard SASE with twelve
segments at 2.1 keV. As expected, the average spectrum width was dominated by
the chirp and was the same in both cases, $0.6\%$ FWHM. However, we could
clearly observe a reduction of number of spikes in spectra. This is
illustrated by Fig. 4 where one can see three representative shots for each
configuration.

###  III.2 Third and fifth harmonic lasing at 4.5 keV

Figure 6: Synchronous scan of phase shifters after first four undulator
segments. The FEL pulse energy is measured after the third part of the
undulator tuned to 4.5 keV. Mean pulse energy and its standard deviation are
plotted versus a delay measured in units of the fundamental wavelength in the
first part (tuned to 900 eV). The FEL operates as a harmonic lasing cascade
(five segments at 900 eV, six segments at 1.5 keV, and six segments at 4.5
keV).

In August 2019 and in February 2020, we went to 4.5 keV lasing on the
fundamental (2.8 Angstrom) and then tested the HLSS scheme on the 3rd
harmonic, by setting the first five segments to 1.5 keV. After that we set
these segments to 900 eV and obtained HLSS operation with the 5th harmonic
seeding. In both cases the fundamental in the first part of the undulator was
well below saturation, so that there were no nonlinear harmonics. We performed
a scan of the K-value of the first part of the undulator, tuned to 900 eV, and
measured the pulse energy after the second part, tuned to 4.5 keV. The result
is presented in Fig. 5. Note that the spectrum at 4.5 keV can not be measured
since the transmission of photon transport line has a cutoff at 3 keV.

Finally, we changed to a Harmonic Lasing Cascade configuration with the five
first segments tuned to 900 eV, the following six segments to 1.5 keV, and the
last six segments to 4.5 keV. In this case the spectral component at 4.5 keV
is amplified as the fifth harmonic in the first part, then as the third
harmonic in the second part with final amplification as the fundamental in the
last part of the undulator. First two cascades are operated in the linear
regime while in the last cascade the nonlinear regime is reached. Pulse energy
was about 200 uJ for the cascade configuration as well as for SASE. In Fig. 6
we show the result of synchronous scan of phase shifters after first four
undulator segments (tuned to 900 eV). FEL pulse energy is measured at 4.5 keV
(after the whole harmonic cascade) versus a delay measured in units of the
fundamental wavelength in the first part. The scans in the second and third
parts of the cascade show, respectively, three cycles (similar to Fig. 3) and
one cycle.

##  IV CONCLUSION AND OUTLOOK

We observed, for the first time, harmonic lasing in the Angstrom regime, both
in Harmonic Lasing Self-Seeded FEL mode and Harmonic Lasing Cascade mode. This
is an important milestone on the way to lasing at high photon energies (up to
100 keV) 100keV  as well as to improving photon beam properties at low
energies hlss . Further steps in this direction will include tests of Harmonic
Lasing Cascade, without lasing on the fundamental in SASE3, as well as
demonstration of harmonic lasing in the hard x-ray undulators of the European
XFEL (SASE1 or SASE2).

##  V ACKNOWLEDGMENT

The authors would like to thank Wim Leemans for the careful reading of the
manuscript and helpful comments.

## References

  * (1) J.B. Murphy, C. Pellegrini and R. Bonifacio, Opt. Commun. 53(1985)197
  * (2) R. Bonifacio, L. De Salvo, and P. Pierini, Nucl. Instr. Meth. A293(1990)627.
  * (3) Z. Huang and K. Kim, Phys. Rev. E, 62(2000)7295.
  * (4) B.W.J. McNeil et al., Phy. Rev. Lett. 96, 084801 (2006)
  * (5) E.A. Schneidmiller and M.V. Yurkov, Phys. Rev. ST-AB 15(2012)080702
  * (6) E.A. Schneidmiller and M.V. Yurkov, ”Harmonic Lasing Self-Seeded FEL”, Proc. of FEL2013, New York, p.700
  * (7) W.B. Colson, IEEE J. Quantum Electron. 17(1981)1417
  * (8) E.A. Schneidmiller et al., Phys. Rev. ST-AB 20(2017)020705
  * (9) W. Ackermann et al., Nature Photonics 1(2007)336
  * (10) B. Faatz et al., New Journal of Physics, 18(2016)062002
  * (11) I. Nam et al., Applied Phys. Lett. 112 (2018) 213506
  * (12) W. Decking et at., Nature Photonics 14(2020)650
  * (13) M. Altarelli et al. (Eds.), XFEL: The European X-Ray Free-Electron Laser. Technical Design Report, Preprint DESY 2006-097, DESY, Hamburg, 2006 (see also http://xfel.desy.de).
  * (14) R. Brinkmann, E.A. Schneidmiller, J. Sekutowicz and M.V. Yurkov, Nucl. Instrum. and Methods A 768(2014)20
  * (15) E.A. Schneidmiller and M.V. Yurkov, Nucl. Instrum. and Methods A 717(2013)37
  * (16) D. Xiang et al., Phys. Rev. ST-AB 16(2013)010703
  * (17) J. Feldhaus et al., Optics. Comm. 140, 341 (1997).
  * (18) G. Geloni, V. Kocharyan and E.L. Saldin, Journal of Modern Optics 58(2011)1391
  * (19) G. Penn, Phys. Rev. ST Accel. Beams 18, 060703 (2015)
  * (20) S. Casalbuoni, “Superconducting undulator development”, presented at the Virtual 2020 Hard X-ray FEL collaboration meeting, Schenefeld (online), 09.09.2020
  * (21) S. Abeghyan et al., J. Synchrotron Rad. 26(2019)302
  * (22) A.A. Sorokin et al., J. Synchrotron Rad. 26 (2019)1092
  * (23) T. Maltezopoulos et al., J. Synchrotron Rad. 26(2019)1045
  * (24) N.Gerasimova ”Performance of the SASE3 monochromator equipped with a provisional short grating. Variable line spacing grating specifications”, Technical Report, 2018, DOI: 10.22003/XFEL.EU-TR-2018-001
  * (25) J. Gruenert et al., J. Synchrotron Rad. 26(2019)1422
  * (26) E.A. Schneidmiller et al., “Feasibility Studies of the 100 keV Undulator Line of the European XFEL”, Proc. of FEL2019, Hamburg, p.172