Cryptographic primitives are the most basic building blocks in cryptographic systems and protocols.
+
Creating cryptographic routines is very hard, and testing them to be reliable takes a long time, it is essentially never sensible (nor secure) to design a new cryptographic primitive to suit the needs of a new cryptographic system.
+
Since algorithms in this field are not only required to be designed well but also need to be tested well by the cryptologist community, even if a cryptographic routine looks good from a design point of view it might still contain errors. Successfully withstanding such scrutiny gives some confidence (in fact, so far, the only confidence) that the algorithm is indeed secure enough to use. Security proofs for cryptographic primitives are generally not available.
+
When choosing primitive for a cryptographic system, one should always consider if the primitive:
+
+
is open and can be studied by the community
+
is patented
+
+
+
+
+
+
Agenda
+
+
+
+
cryptographic hash functions
+
cryptographically secure random generators (the very basics)
+
symmetric ciphers
+
public-key cryptography / asymmetric cryptography
+
+
+
+
+
+
+
Cryptographic hash functions
+
A hash function is any function that can be used to map data of arbitrary size to fixed-size values (or a set of fixed-size values)
+
Cryptographic hash functions or cryptographically secure hash functions are hash functions with special properties (making them desirable for cryptographic systems)
+
(Over)simplified list of desired properties:
+
+
the probability of a particular output result (hash value) n for a random input string ("message") is 2^(-n) (as for any good hash), so the hash value can be used as a representative of the message
+
given a hash value h, it should be difficult to find any message m such that h = hash(m) ("reversing the function")
+
given an input m1, it should be difficult to find a different input m2 such that hash(m1) = hash(m2) (weak collision resistance)
+
it should be difficult to find two different messages m1 and m2 such that hash(m1) = hash(m2). Such a pair is called a cryptographic hash collision (strong collision resistance)
+
if an input is changed slightly (for example, flipping a single bit), the output changes significantly (avalanche effect) - a property also desired in ciphers
+
being fast is always a nice bonus :)
+
+
+
+
+
+
MD5
+
+
+
+
Designers
+
Ronald Rivest
+
+
+
Published
+
1992
+
+
+
Digest size
+
128 bits (16 bytes)
+
+
+
Block size
+
512 bits
+
+
+
Broken?
+
Yes (broken collision resistance in 2^18 time)
+
+
+
+
Still in use in lagacy applications and in applications where collision resistance is not needed
+
+
+
+
+
SHA-1 (Secure Hash Algorithm 1)
+
+
+
+
Designers
+
NSA
+
+
+
Published
+
1995
+
+
+
Digest size
+
160 bits (20 bytes)
+
+
+
Block size
+
512 bits
+
+
+
Broken?
+
Yes
+
+
+
+
Still in use. Revision control systems such as Git, Mercurial, and Monotone use SHA-1, not for security, but to identify revisions and to ensure that the data has not changed due to accidental corruption.
+
+
+
+
+
SHA-2
+
+
+
+
Designers
+
NSA
+
+
+
Published
+
2001
+
+
+
Digest sizes
+
224, 256, 384 or 512 bits
+
+
+
Block sizes
+
256 bits (SHA-224 and SHA-256) or 512 bits
+
+
+
Broken?
+
No
+
+
+
+
+
+
+
+
SHA-3
+
+
+
+
Designers
+
Guido Bertoni, Joan Daemen, Michaël Peeters and Gilles van Assche
+
+
+
Published
+
2016
+
+
+
Digest size
+
arbitrary
+
+
+
Block size
+
variable
+
+
+
Broken?
+
No
+
+
+
+
Based on Keccak - winner of the NIST hash function competition after some controversial adjustments. Very fast and flexible.
+
+
+
+
+
BLAKE2
+
+
+
+
Designers
+
Jean-Philippe Aumasson, Samuel Neves, Zooko Wilcox-O'Hearn and Christian Winnerlein
+
+
+
Published
+
2008 (BLAKE)
+
+
+
Digest size
+
up to 64 bytes (BLAKE2b), up to 32 bytes (BLAKE2s)
+
+
+
Block size
+
variable (stream)
+
+
+
Broken?
+
No
+
+
+
+
BLAKE2 is based on Daniel J. Bernstein's ChaCha stream cipher and is extremely fast. BLAKE2b and BLAKE2s are specified in RFC 7693.
+
+
+
+
+
Application of cryptographic hash functions
+
Cryptographic hash functions are used for many different things in cryptographic systems and protocols. Few examples:
+
+
can be used directly (f.i. sha256sum - part of GNU coreutils)
+
message authentication code (MAC)
+
password hashing / key derivation
+
proof of work
+
+
+
+
+
+
HMAC
+
HMAC (hash-based message authentication code) is a specific type of message authentication code (MAC) involving a cryptographic hash function and a secret cryptographic key. It may be used to simultaneously verify both the data integrity and authenticity of a message. An HMAC is a type of keyed hash function that can also be used in a key derivation scheme or a key stretching scheme.
+
HMAC can provide authentication using a shared secret instead of using digital signatures with asymmetric cryptography. It trades off the need for a complex public key infrastructure by delegating the key exchange to the communicating parties, who are responsible for establishing and using a trusted channel to agree on the key prior to communication.
+
+
XOR (eXclusive OR) - is a logical operator
+
With two inputs, XOR is true if and only if the inputs differ (one is true, one is false)
+
+
+
+
+
PBKDF2
+
PBKDF2 (Password-Based Key Derivation Function 2) is a key derivation function with a sliding computational cost, used to reduce vulnerability to brute-force attacks.
+
PBKDF2 applies a pseudorandom function, such as HMAC, to the input password or passphrase along with a salt value and repeats the process many times to produce a derived key, which can then be used as a cryptographic key in subsequent operations. The added computational work makes password cracking much more difficult, and is known as key stretching.
+
Demo
+
+
+
+
+
Proof of work
+
Demo
+
+
+
+
+
Cryptographically secure random generators
+
Most cryptographic applications require random numbers for: generating keys, initialization vectors, nonces (arbitrary numbers that can be used just once), salts, tokens etc, etc.
+
The "quality" of the randomness required for these applications varies. For example, creating a nonce in some protocols needs only uniqueness. On the other hand, the generation of a master key requires a higher quality, such as more entropy.
+
Entropy is obtained from a high-quality source, generally the operating system's randomness API.
+
In practical situations, numbers are needed with more randomness than the available entropy can provide. Also, the processes to extract randomness from a running system are slow in actual practice. In such instances, a cryptographically secure pseudorandom number generator (CSPRNG) can sometimes be used. A CSPRNG can "stretch" the available entropy over more bits.
+
+
+
+
+
Cryptographically secure random generators (cont...)
+
CSPRNG must
+
+
pass statistical randomness tests - f.i. given the first k bits of a random sequence, there is no polynomial-time algorithm that can predict the (k+1)th bit with probability of success non-negligibly better than 50%
+
be attack resistant - f.i. in the event that part or all of its state has been revealed it should be impossible to reconstruct the stream of random numbers prior to the revelation
+
+
"Practical" CSPRNG schemes not only include an CSPRNG algorithm, but also a way to initialize ("seed") it while keeping the seed secret.
+
+
+
+
+
Symmetric-key algorithms / symmetric ciphers
+
Symmetric ciphers use the same cryptographic keys for both the encryption of plaintext and the decryption of ciphertext.
+
“Anyone, from the most clueless amateur to the best cryptographer, can create an algorithm that he himself can’t break. It’s not even hard. What is hard is creating an algorithm that no one else can break, even after years of analysis. And the only way to prove that is to subject the algorithm to years of analysis by the best cryptographers around.” - Bruce Schneier
+
A "perfect" cipher - the one-time pad has been known since 1882, but is not practically applicable in modern systems:
+
+
generate random stream (pad) with length = len(plaintext)
+
ciphertext = plaintext XOR pad
+
+
+
There two types of modern ciphers:
+
+
block ciphers - operate on fixed-length groups of bits, called blocks (padding may be used to the remaining bits)
+
stream ciphers - plaintext digits are combined (XORed) with a pseudorandom cipher digit stream (keystream).
+
+
+
+
+
+
DES (Data Encryption Standard)
+
+
+
+
Designer
+
IBM
+
+
+
Published
+
1975
+
+
+
Key size
+
56 bits
+
+
+
Block size
+
64 bits
+
+
+
+
Not used anymore. 3DES was published in 1981 (with keysize of 112 bits or 168 bits).
+
+
+
+
+
Blowfish
+
+
+
+
Designer
+
Bruce Schneier
+
+
+
Published
+
1993
+
+
+
Key size
+
32-448 bits
+
+
+
Block size
+
64 bits
+
+
+
+
Schneier has stated that "Blowfish is unpatented, and will remain so in all countries. The algorithm is hereby placed in the public domain, and can be freely used by anyone.". Still in use in legacy applications and notably in the bcrypt password hashing function.
+
+
+
+
+
AES (Advanced Encryption Standard - Rijndael)
+
+
+
+
Designer
+
Joan Daemen, Vincent Rijmen
+
+
+
Published
+
1998
+
+
+
Key size
+
128 bits, 192 bits or 256 bits
+
+
+
Block size
+
128 bits
+
+
+
+
Rijndael was thew winner of the NIST AES selection process. Currently the most widely adopted block cipher (both in hardware and software).
+
+
+
+
+
Mode of operation for block ciphers
+
A block cipher by itself is only suitable for the secure cryptographic transformation (encryption or decryption) of one fixed-length group of bits called a block. A mode of operation describes how to repeatedly apply a cipher's single-block operation to securely transform amounts of data larger than a block.
+
+
+
+
+
+
Galois/counter (GCM)
+
The GCM algorithm provides both data authenticity (integrity) and confidentiality and belongs to the class of authenticated encryption with associated data (AEAD) methods. This means that as input it takes a key K, some plaintext P, and some associated data AD; it then encrypts the plaintext using the key to produce ciphertext C, and computes an authentication tag T from the ciphertext and the associated data (which remains unencrypted). A recipient with knowledge of K, upon reception of AD, C and T, can decrypt the ciphertext to recover the plaintext P and can check the tag T to ensure that neither ciphertext nor associated data were tampered with.
+
+
+
+
+
+
Stream ciphers
+
Stream ciphers typically execute at a higher speed than block ciphers and have lower hardware complexity. However, stream ciphers can be susceptible to security breaches, for example, when the same starting state (seed) is used twice.
+
Essentially they behave as pseudorandom functions where they key is / is part of the "seed".
+
+
ChaCha
+
+
+
+
Designer
+
Daniel J. Bernstein (djb)
+
+
+
Published
+
2008
+
+
+
Key size
+
128 bits or 256 bits
+
+
+
+
+
ChaCha20-Poly1305
+
ChaCha20-Poly1305 is an AEAD algorithm, that combines the ChaCha20 stream cipher with the Poly1305 message authentication code. It has fast software performance, and without hardware acceleration, is usually faster than AES-GCM.
+
+
+
+
+
Public-key cryptography / asymmetric cryptography
+
Asymmetric cryptography makes use pairs of related keys. Each key pair consists of a public key and a corresponding private key. Key pairs are generated with cryptographic algorithms based on mathematical problems termed one-way functions. Security of public-key cryptography depends on keeping the private key secret, while the public key can be openly distributed without compromising security.
+
Usually we use public key cryptography for:
+
+
public key encryption - a message is encrypted with the intended recipient's public key. For properly chosen and used algorithms, messages cannot in practice be decrypted by anyone who does not possess the matching private key, who is thus presumed to be the owner of that key and so the person associated with the public key. This can be used to ensure confidentiality of a message.
+
digital signatures - a message is signed with the sender's private key and can be verified by anyone who has access to the sender's public key. This verification proves that the sender had access to the private key, and therefore is very likely to be the person associated with the public key. It also proves that the signature was prepared for that exact message, since a signature that passes verification with the public key on one message will not pass verification with the public key on other messages.
+
+
+
+
+
+
RSA
+
+
+
+
Designers
+
Ron Rivest, Adi Shamir and Leonard Adleman
+
+
+
Published
+
1977, patented until 2000 :(
+
+
+
+
+
Using RSA
+
The security of RSA relies on the practical difficulty of factoring the product of two large prime numbers, the "factoring problem". Still the most widely used public key system. A key size of 2048 or 4096 bits should be used. A very good CSPRNG is needed.
+
In modern cryptographic systems and protocols (like TLS >= 1.2) RSA is only used for verifying that the client is initiating session with the "right" server. Encryption / decryption is performed using common negotiated key and symmetric ciphers.
+
Forward secrecy is a desired feature of specific key-agreement protocols that gives assurances that session keys will not be compromised even if long-term secrets used in the session key exchange are compromised, limiting damage. For HTTPS, the long-term secret is typically the private key of the server.
+
+
+
+
+
Post-quantum cryptography
+
Post-quantum cryptography (PQC), sometimes referred to as quantum-proof, quantum-safe, or quantum-resistant, is the development of cryptographic algorithms (usually public-key algorithms) that are thought to be secure against a cryptanalytic attack by a quantum computer. Most widely-used public-key algorithms rely on the difficulty of one of three mathematical problems: the integer factorization problem, the discrete logarithm problem or the elliptic-curve discrete logarithm problem. All of these problems could be easily solved on a sufficiently powerful quantum computer running Shor's algorithm or even faster and less demanding (in terms of the number of qubits required) alternatives.
- reveal.js enables you to create beautiful interactive slide decks using HTML. This presentation will show you examples of what it can do.
+ reveal.js enables you to create beautiful interactive slide decks using HTML. This
+ presentation will show you examples of what it can do.
- Not a coder? Not a problem. There's a fully-featured visual editor for authoring these, try it out at https://slides.com.
+ Not a coder? Not a problem. There's a fully-featured visual editor for authoring these,
+ try it out at https://slides.com.
Hidden Slides
- This slide is visible in the source, but hidden when the presentation is viewed. You can show all hidden slides by setting the `showHiddenSlides` config option to `true`.
+ This slide is visible in the source, but hidden when the presentation is viewed. You can
+ show all hidden slides by setting the `showHiddenSlides` config option to `true`.
- Set data-background="#dddddd" on a slide to change the background color. All CSS color formats are supported.
+ Set data-background="#dddddd" on a slide to change the background color.
+ All CSS color formats are supported.
- Different background transitions are available via the backgroundTransition option. This one's called "zoom".
+ Different background transitions are available via the backgroundTransition option. This
+ one's called "zoom".
- You can override background transitions per-slide.
-
+
You can override background transitions per-slide.
<section data-background-transition="zoom">
-
+
Iframe Backgrounds
-
Since reveal.js runs on the web, you can easily embed other web content. Try interacting with the page in the background.
+
+ Since reveal.js runs on the web, you can easily embed other web content. Try
+ interacting with the page in the background.
+
@@ -372,11 +558,19 @@
Clever Quotes
- These guys come in two forms, inline: The nice thing about standards is that there are so many to choose from and block:
+ These guys come in two forms, inline:
+ The nice thing about standards is that there are so many to choose from
+ and block:
-
- “For years there has been a theory that millions of monkeys typing at random on millions of typewriters would
- reproduce the entire works of Shakespeare. The Internet has proven this theory to be untrue.”
+
+ “For years there has been a theory that millions of monkeys typing at random on
+ millions of typewriters would reproduce the entire works of Shakespeare. The Internet
+ has proven this theory to be untrue.”
@@ -390,18 +584,36 @@
Speaker View
-
There's a speaker view. It includes a timer, preview of the upcoming slide as well as your speaker notes.
+
+ There's a speaker view. It includes a
+ timer, preview of the upcoming slide as well as your speaker notes.
+
+ Presentations can be exported to PDF,
+ here's an example:
+
+
@@ -416,7 +628,8 @@
State Events
- Additionally custom events can be triggered on a per slide basis by binding to the data-state name.
+ Additionally custom events can be triggered on a per slide basis by binding to the
+ data-state name.
- Press B or . on your keyboard to pause the presentation. This is helpful when you're on stage and want to take distracting slides off the screen.
+ Press B or . on your keyboard to pause the presentation. This is helpful when you're on
+ stage and want to take distracting slides off the screen.
-
- # shell
- # fetch information from the OS
- cat /proc/cpuinfo
- cat /proc/meminfo
- sysctl -a | grep shm # get kernel parameters of interest
-
-
- -- SQL
- -- show the current values of all settings
- SHOW ALL;
-
- -- display even more than all...
- SELECT * FROM pg_settings;
-
- -- opening postgresql.conf and reading the comments - the old school approach
-
-
- # old school: edit postgresql.conf and read the comments
-
+
+ # shell
+ # fetch information from the OS
+ cat /proc/cpuinfo
+ cat /proc/meminfo
+ sysctl -a | grep shm # get kernel parameters of interest
+
+
+ -- SQL
+ -- show the current values of all settings
+ SHOW ALL;
+
+ -- display even more than all...
+ SELECT * FROM pg_settings;
+
+ -- opening postgresql.conf and reading the comments - the old school approach
+
+
+ # old school: edit postgresql.conf and read the comments
+
-
+
-
-
Memory settings
+
+
Memory settings
-
shared_buffers - how much memory is dedicated to PostgreSQL to use for caching data - for a system with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the system memory (128MB -> 1GB)
-
effective_cache_size - how much memory we expect to be available in the OS and PostgreSQL buffer caches, not an allocation - used only by the PostgreSQL query planner to figure out whether plans it's considering would be expected to fit in RAM or not - 1/2 of total memory would be a normal conservative setting (4GB -> 8GB)
-
work_mem - the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files - for a complex query, several sort or hash operations might be running in parallel; each operation will generally be allowed to use as much memory as this value specifies (4MB -> 8MB)
-
maintenance_work_mem - the maximum amount of memory to be used by maintenance operations, such as VACUUM and CREATE INDEX. It's safe to set this value significantly larger than work_mem (64MB -> 256MB)
+
shared_buffers - how much memory is dedicated to PostgreSQL to use for caching data - for a system with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the system memory (128MB -> 1GB)
+
effective_cache_size - how much memory we expect to be available in the OS and PostgreSQL buffer caches, not an allocation - used only by the PostgreSQL query planner to figure out whether plans it's considering would be expected to fit in RAM or not - 1/2 of total memory would be a normal conservative setting (4GB -> 8GB)
+
work_mem - the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files - for a complex query, several sort or hash operations might be running in parallel; each operation will generally be allowed to use as much memory as this value specifies (4MB -> 8MB)
+
maintenance_work_mem - the maximum amount of memory to be used by maintenance operations, such as VACUUM and CREATE INDEX. It's safe to set this value significantly larger than work_mem (64MB -> 256MB)
-
+
-
-
Logging and performance reports
+
+
Logging and performance reports
-
pgBadger - a fast PostgreSQL log analysis report can be used for general analysis.
-
-
log_checkpoints - checkpoints and restartpoints are logged in the server log. Some statistics are included in the log messages, including the number of buffers written and the time spent writing them
-
log_connections - each attempted connection to the server to be logged, as well as successful completion of client authentication
-
log_disconnections - provides information similar to log_connections, plus the duration of the session
-
log_line_prefix - set to '%t [%p]: user=%u,db=%d,app=%a,client=%h '
-
log_lock_waits - log message is produced when a session waits longer than deadlock_timeout to acquire a lock. This is useful in determining if lock waits are causing poor performance
-
log_temp_files - when set to 0, a log entry is emitted for each temporary file when it is deleted
-
log_autovacuum_min_duration - set to 0 it logs all autovacuum actions
-
-
-
-
-
Other tools for analysis
+
pgBadger - a fast PostgreSQL log analysis report can be used for general analysis.
+
+
log_checkpoints - checkpoints and restartpoints are logged in the server log. Some statistics are included in the log messages, including the number of buffers written and the time spent writing them
+
log_connections - each attempted connection to the server to be logged, as well as successful completion of client authentication
+
log_disconnections - provides information similar to log_connections, plus the duration of the session
+
log_line_prefix - set to '%t [%p]: user=%u,db=%d,app=%a,client=%h '
+
log_lock_waits - log message is produced when a session waits longer than deadlock_timeout to acquire a lock. This is useful in determining if lock waits are causing poor performance
+
log_temp_files - when set to 0, a log entry is emitted for each temporary file when it is deleted
+
log_autovacuum_min_duration - set to 0 it logs all autovacuum actions
+
+
+
+
+
Other tools for analysis
-
ANALYZE - collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.
-
VACUUM - reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. (VACUUM vs. VACUUM FULL)
+
ANALYZE - collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.
+
VACUUM - reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. (VACUUM vs. VACUUM FULL)
Køer defineres som durable ved hjelp av webgrensesnittet.
-
"Alle køer (både forhåndsdefinerte og de som defineres av konsument) må ha navn som starter med q_. Køen vil ha navn som uttryker mottakeren som bruker den."
-
F.eks. q_ad_ms_all vil være et passende navn for køen som brukes av AD-microservice mottakeren.
-
-
-
-
Exchange
-
"Avsendere vil vanligvis sende meldinger til exchange ex_messages."
-
Det er flere typer exchange, men vi vil bruke kun topic exchange.
-
-
-
-
-
-
Utvikling
-
-
-
Routing keys i topic exchange
-
Bindinger
-
Protokoller og porter
-
Eksempler
-
-
-
-
-
Routing keys i topic exchange
-
-
Routing key settes av sender som en del av meldingen og blir inspisert av brokeren dersom meldingen sendes til en topic exchange.
-
Strukturen til en topic / message routing key er:
-
<kilde>.<type>.<objekt>.<hendelse>
-
F.eks.:
-
cerebrum.event.person.delete
-
-
-
-
Bindinger
-
For at en melding sendt til en topic exchange skal havne i en bestemt kø, må køen være bundet (bound) til exchange.
-
I topic exchange brukes routing key (topic) til å avgjøre hvilke av meldingene som blir sendt vil havne i køen som er bundet.
-
Dersom vi binder køen q_ad_ms_all til topic exchangeex_messages med binding keycerebrum.event.account.* vil alle meldinger som blir sendt til ex_messages med topic som starter med cerebrum.event.account. havne i q_ad_ms_all.
-
En kø kan bli bundet med én eller flere nøkler til en topic exchange.
-
-
-
-
Protokoller og porter
-
-
"AMQP 0.9.1 prioritert siden det er protokollen som mapper best mot RabbitMQs funksjonalitet. Vi har også noe erfaring med bruk av denne."
SQLAlchemy is a Python library created by Mike Bayer to provide a high-level Pythonic interface to RDBMS such as PostgreSQL, SQLite, MySQL, Oracle, DB2.
-
SQLAlchemy includes RDBMS-independent SQL expression language and an object-relational mapper (ORM).
-
+
SQLAlchemy is a Python library created by Mike Bayer to provide a high-level Pythonic interface to RDBMS such as PostgreSQL, SQLite, MySQL, Oracle, DB2.
+
SQLAlchemy includes RDBMS-independent SQL expression language and an object-relational mapper (ORM).
+
-
-
Why use SQLAlchemy?
+
+
Why use SQLAlchemy?
-
free software - free as in "freedom" (MIT licensed)
-
portability - the programming interface is independent of the type of RDBMS and connector used
-
security - no more SQL injections
-
abstraction - no need to bother with complex JOINs
-
object-orientation - you work with objects instead of tables and rows
-
performance - exploits the likehood of reusing a particular query
-
flexibility - you can override almost anything
+
free software - free as in "freedom" (MIT licensed)
+
portability - the programming interface is independent of the type of RDBMS and connector used
+
security - no more SQL injections
+
abstraction - no need to bother with complex JOINs
+
object-orientation - you work with objects instead of tables and rows
+
performance - exploits the likehood of reusing a particular query
+
flexibility - you can override almost anything
-
-
-
-
Basic architecture
-
SQLAlchemy consists of several components, including the ORM.
-
-
Engine- manages the connection pool and the RDBMS-independent SQL dialect layer
-
MetaData - used to collect and organize information about your table layout (schema)
-
SQL expression language - provides an API to execute your queries and updates against your tables, all from Python, and all in a database-independent way (low-level interface)
-
ORM - provides a convenient way to add database persistence to your Python objects without requiring you to design your objects around the database, or the database around the objects (high-level interface)
-
Session - establishes all conversations with the RDBMS and represents a "holding zone" for all the objects which you've loaded or associated with it during its lifespan
-
-
-
-
-
-
Example
-
SQLAlchemy gives us the choice between classical mapping and the newer declarative mapping
-
- import pandas as pd
-
- session.query(ProductionPlan).order_by(ProductionPlan.start_time) # returns a Query instance
- session.query(ProductionPlan).order_by(ProductionPlan.start_time).all() # returns an object-list
-
- # return all production plans where start_time after 2022-09-01 00:00
- session.query(ProductionPlan).filter(ProductionPlan.start_time > datetime.datetime(2022, 9, 1, 0, 0)).all()
-
- # return production plans with value > 80
- query = session.query(ProductionPlan).filter(ProductionPlan.value > 80).order_by(ProductionPlan.start_time)
- query.count() # returns 1
- production_plan = query.first() # returns the first object (element)
- production_plan = query.one() # raises NoResultFound exception or MultipleResultsFound in case elements != 1
-
- # generate Pandas DataFrame from a query or entire table
- df = pd.read_sql_table("my_table", con=session.get_bind()) # or con=engine
- df = pd.read_sql_query(query.statement, engine)
-
- # return production plans with production type 'B37'
- session.query(ProductionPlan).filter(
- ProductionPlan.production_type_id == ProductionType.production_type_id
- ).filter(ProductionType.code == "B37").all()
- session.query(ProductionPlan).join(ProductionType).filter(ProductionType.code == "B37").all()
- session.query(ProductionPlan).filter(ProductionPlan.production_type == production_type_B37).all()
- session.query(
- ProductionPlan
- ).from_statement(
- text(
- "SELECT pp.* FROM production_plans pp, production_types pt "
- "WHERE pp.production_type_id = pt.production_type_id AND pt.code=:code"
- )
- ).params(code="B37").all()
-
-
-
-
-
-
Q & A
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
Basic architecture
+
SQLAlchemy consists of several components, including the ORM.
+
+
Engine- manages the connection pool and the RDBMS-independent SQL dialect layer
+
MetaData - used to collect and organize information about your table layout (schema)
+
SQL expression language - provides an API to execute your queries and updates against your tables, all from Python, and all in a database-independent way (low-level interface)
+
ORM - provides a convenient way to add database persistence to your Python objects without requiring you to design your objects around the database, or the database around the objects (high-level interface)
+
Session - establishes all conversations with the RDBMS and represents a "holding zone" for all the objects which you've loaded or associated with it during its lifespan
+
+
+
+
+
+
Example
+
SQLAlchemy gives us the choice between classical mapping and the newer declarative mapping
Tomas Robertson (team ACE-OL) & Simeon Simeonov (team Forecasts)
-
-
-
-
-
What is time travel???
-
-
Time travel - our ability to look at our input data at the state it was at a specific point in time (not only at its last state).
-
Time travel is achieved by adding the record_created_time column to our DB tables and storing Kafka's created_time value (converted to UTC).
-
-
The main concepts around how and why were presented by Peter Sandberg.
-
-
-
-
Solving problems
-
-
The following spike was observed 2021-10-06 around 10:15 CET @ NO4
-
-
-
-
-
Solving problems (cont)
-
-
No spikes shown in Grafana
-
-
-
-
-
Solving problems with time travel
-
-
-
- SELECT record_created_time, start_time, value
- FROM misc.app_odin_ace_ol_ba_10s_avro_v01
- WHERE bidding_area_name = 'NO4' AND start_time = '2021-10-06 08:14:10'
- ORDER BY record_created_time;
-
-
+ 
+ 
+ 
+ 
+ 
+ 
+ 
- 
- 
- 
-