NoSQL - Primary Key/Composite Key/Partition Key/Clustering Key

The primary key is a general concept to indicate one or more columns used to retrieve data from a Table.

The primary key may be SIMPLE

 create table stackoverflow (
      key text PRIMARY KEY,
      data text    
  );
That means that it is made by a single column.

But the primary key can also be COMPOSITE (aka COMPOUND), generated from more columns.

 create table stackoverflow (
      key_part_one text,
      key_part_two int,
      data text,
      PRIMARY KEY(key_part_one, key_part_two)    
  );
In a situation of COMPOSITE primary key, the "first part" of the key is called PARTITION KEY (in this example key_part_one is the partition key) and the second part of the key is the CLUSTERING KEY (key_part_two)

Please note that the both partition and clustering key can be made by more columns

 create table stackoverflow (
      k_part_one text,
      k_part_two int,
      k_clust_one text,
      k_clust_two int,
      k_clust_three uuid,
      data text,
      PRIMARY KEY((k_part_one,k_part_two), k_clust_one, k_clust_two, k_clust_three)    
  );
Behind these names ...

The Partition Key is responsible for data distribution accross your nodes.
The Clustering Key is responsible for data sorting within the partition.
The Primary Key is equivalent to the Partition Key in a single-field-key table.
The Composite/Compund Key is just a multiple-columns key

Usage and content examples

SIMPLE KEY:
insert into stackoverflow (key, data) VALUES ('han', 'solo');
select * from stackoverflow where key='han';
table content

key | data
----+------
han | solo

COMPOSITE/COMPOUND KEY can retrieve "wide rows"

insert into stackoverflow (key_part_one, key_part_two, data) VALUES ('ronaldo', 9, 'football player');
insert into stackoverflow (key_part_one, key_part_two, data) VALUES ('ronaldo', 10, 'ex-football player');
select * from stackoverflow where key_part_one = 'ronaldo';

table content

 key_part_one | key_part_two | data
--------------+--------------+--------------------
      ronaldo |            9 |    football player
      ronaldo |           10 | ex-football player
But you can query with all key ...

select * from stackoverflow where key_part_one = 'ronaldo' and key_part_two  = 10;
query output

 key_part_one | key_part_two | data
--------------+--------------+--------------------
      ronaldo |           10 | ex-football player
     
Important note: the partition key is the minimum-specifier needed to perform a query using where clause. If you have a composite partition key, like the following

eg: PRIMARY KEY((col1, col2), col10, col4))

You can perform query only passing at least both col1 and col2, these are the 2 columns that defines the partition key. The "general" rule to make query is you have to pass at least all partition key columns, then you can add each key in the order they're set.

NOSQL : CQLSH Commands

Cqlsh - Start the CQL interactive terminal.

Syntax : cqlsh [options] [host [port]]

Example

/opt/cassandra/dse-4.8.2/bin/cqlsh optrhdbcasra01adev -u cswgadmin -p admin123

CAPTURE - Captures command output and appends it to a file.

To start capturing the output of a query, specify the path of the file relative to the current directory. Enclose the file name in single quotation marks. The shorthand notation in this example is supported for referring to $HOME.

Output is not shown on the console while it is captured. Only query result output is captured. Errors and output from cqlsh-only commands still appear. To stop capturing output and return to normal display of output, use CAPTURE OFF.

Example :  CAPTURE '~/mydir/myfile.txt'

COPY -

Imports and exports CSV (comma-separated values) data to and from Cassandra.

COPY table_name ( column, ...) FROM ( 'file_name' | STDIN ) WITH option = 'value' AND ...

COPY table_name ( column , ... ) TO ( 'file_name' | STDOUT ) WITH option = 'value' AND …

Example :

CREATE KEYSPACE test

  WITH REPLICATION = { 'class' : 'NetworkTopologyStrategy', 'datacenter1' : 1 };

USE test;

CREATE TABLE airplanes (

  name text PRIMARY KEY,

  manufacturer ascii,

  year int,

  mach float

);

INSERT INTO airplanes

  (name, manufacturer, year, mach)

  VALUES ('P38-Lightning', 'Lockheed', 1937, 0.7);

COPY airplanes (name, manufacturer, year, mach) TO 'temp.csv';

TRUNCATE airplanes;

COPY airplanes (name, manufacturer, year, mach) FROM 'temp.csv';

TRUNCATE airplanes;

COPY airplanes (name, manufacturer, year, mach) FROM STDIN;

The output is:

[Use \. on a line by itself to end input]

[copy]

At the [copy] prompt, enter the following data:

'F-14D Super Tomcat', Grumman, 1987, 2.34

'MiG-23 Flogger', Russian-made, 1964, 2.35

'Su-27 Flanker', U.S.S.R., 1981, 2.35

\.

DESCRIBE : Provides information about the connected Cassandra cluster, or about the data objects stored in the cluster.

DESCRIBE FULL ( CLUSTER | SCHEMA )

| KEYSPACES

| ( KEYSPACE keyspace_name )

| TABLES

| ( TABLE table_name )

| TYPES

| ( TYPE user_defined_type )

| INDEX

| ( INDEX index_name )

EXPAND - Formats the output of a query vertically.

This command lists the contents of each row of a table vertically, providing a more convenient way to read long rows of data than the default horizontal format. You scroll down to see more of the row instead of scrolling to the right. Each column name appears on a separate line in column one and the values appear in column two.

EXPAND ON | OFF

PAGING - Enables or disables query paging.

PAGING ON|OFF

SHOW - Shows the Cassandra version, host, or tracing information for the current cqlsh client session.

SHOW VERSION

| HOST

| SESSION tracing_session_id

SOURCE - Executes a file containing CQL statements.

SOURCE '~/mydir/myfile.txt'

Oracle SQL to find a Table size

Here you go,


select SEGMENT_NAME, round((bytes*0.000001),2) size_in_mb , round((bytes*0.000001),2) *0.001 size_in_gb from dba_SEGMENTS
where SEGMENT_NAME = 'Table Name'

Overview of SOAP and REST Services, Differences

SOAP : Simple Object Access Protocol

SOAP is a standards-based Web services access protocol that has been around for a while and enjoys all of the benefits of long-term use. Originally developed by Microsoft, SOAP really isn’t as simple as the acronym would suggest.

REST : Representational State Transfer

The acronym REST stands for Representational State Transfer, this basically means that each unique URL is a representation of some object. You can get the contents of that object using an HTTP GET, to delete it, you then might use a POST, PUT, or DELETE to modify (CRUD operations) the object.

Overview of SOAP

SOAP relies exclusively on XML to provide messaging services. Microsoft originally developed SOAP to take the place of older technologies that don’t work well on the Internet such as the Distributed Component Object Model (DCOM) and Common Object Request Broker Architecture (CORBA). These technologies fail because they rely on binary messaging; the XML messaging that SOAP employs works better over the Internet.

SOAP is designed to support expansion, so it has all sorts of other acronyms and abbreviations associated with it, such as WS-Addressing, WS-Policy, WS-Security, WS-Federation, WS-ReliableMessaging, WS-Coordination, WS-AtomicTransaction, and WS-RemotePortlets.

The point is that SOAP is highly extensible, but you only use the pieces you need for a particular task. For example, when using a public Web service that’s freely available to everyone, you really don’t have much need for WS-Security.

The XML used to make requests and receive responses in SOAP can become extremely complex. In some programming languages, you need to build those requests manually, which becomes problematic because SOAP is intolerant of errors. However, other languages can use shortcuts that SOAP provides; that can help you reduce the effort required to create the request and to parse the response. In fact, when working with .NET languages, you never even see the XML.

Part of the magic is the Web Services Description Language (WSDL). This is another file that’s associated with SOAP. It provides a definition of how the Web service works, so that when you create a reference to it, the IDE can completely automate the process. So, the difficulty of using SOAP depends to a large degree on the language you use.

One of the most important SOAP features is built-in error handling. If there’s a problem with your request, the response contains error information that you can use to fix the problem. Given that you might not own the Web service, this particular feature is extremely important; otherwise you would be left guessing as to why things didn’t work. The error reporting even provides standardized codes so that it’s possible to automate some error handling tasks in your code.

An interesting SOAP feature is that you don’t necessarily have to use it with the HyperText Transfer Protocol (HTTP) transport. There’s an actual specification for using SMTP,


Overview of REST

Many developers found SOAP cumbersome and hard to use. For example, working with SOAP in JavaScript means writing a ton of code to perform extremely simple tasks because you must create the required XML structure absolutely every time.

REST provides a lighter weight alternative. Instead of using XML to make a request, REST relies on a simple URL in many cases. In some situations you must provide additional information in special ways, but most Web services using REST rely exclusively on obtaining the needed information using the URL approach. REST can use four different HTTP 1.1 verbs (GET, POST, PUT, and DELETE) to perform tasks.

Unlike SOAP, REST doesn’t have to use XML to provide the response. You can find REST-based Web services that output the data in Command Separated Value (CSV), JavaScript Object Notation (JSON) and Really Simple Syndication (RSS). The point is that you can obtain the output you need in a form that’s easy to parse within the language you need for your application.

Soap Vs Rest

SOAP is definitely the heavyweight choice for Web service access. It provides the following advantages when compared to REST.

• Language, platform, and transport independent (REST requires use of HTTP)
• Works well in distributed enterprise environments (REST assumes direct point-to-point communication)
• Standardized
• Provides significant pre-build extensibility in the form of the WS* standards
• Built-in error handling
• Automation when used with certain language products
• Easy to consume - sometimes
• Rigid - type checking, adheres to a contract
• Development tools

REST is easier to use for the most part and is more flexible. It has the following advantages when compared to SOAP:

• No expensive tools require to interact with the Web service
• Smaller learning curve
• Efficient (SOAP uses XML for all messages, REST can use smaller message formats)
• Fast (no extensive processing required)
• Closer to other Web technologies in design philosophy
• Lightweight - not a lot of extra xml markup
• Human Readable Results
• Easy to build - no toolkits required

Oracle SQL to find the dependencies of an Object/Table

User following SQL to find the dependencies of an object/ta ble.


SELECT
  *
FROM
  sys.dba_dependencies
WHERE
  1                         =1
-- AND referenced_type = 'TABLE'
AND referenced_owner        = 'APPS'
AND UPPER(referenced_name) IN UPPER('XXINV_ITEMS_OUTBOUND_STG');