Bigdata ideas

I have one single instance Cassandra with source data, only 3 CF: There are 3 tables, mts_src.ticks - contains information about Forex tick data for 28 tickers, db_tsunx - Unixtimestamp. mts_src.ticks_count_days - Total ticks count by day and ticker_id. mts_src.ticks_count_total - Total ticks count by ticker_id. DROP TABLE mts_src.ticks; DROP TABLE mts_src.ticks_count_days; DROP TABLE mts_src.ticks_count_total; CREATE TABLE mts_src.ticks( ticker_id int , ddate date , ts bigint, db_tsunx bigint, ask double , bid double , PRIMARY KEY (( ticker_id, ddate ), ts, db_tsunx) ) WITH CLUSTERING ORDER BY ( ts DESC , db_tsunx DESC ); CREATE TABLE mts_src.ticks_count_days( ticker_id int , ddate date , ticks_count counter, PRIMARY KEY (ticker_id, ddate) ) WITH CLUSTERING ORDER BY ( ddate DESC ); CREATE TABLE mts_src.ticks_count_total( ticker_id int , ticks_count counter, PRIMARY KEY (ticker_id) ); Data from mts_src.ticks is using for bar...

I will use test data like in  the previous article (KMeans) We will train model, check it on test data and finally use it to classify (or clustering) points one by one. Generate test data set that contains 3 areas, drop table delit_test_data; create table delit_test_data as --cluster 1 select 1 as cluster_number, ROUND(2+(case when dbms_random.value<0.5 then -1 else +1 end)*dbms_random.value*1.5,2) as x, ROUND(2+(case when dbms_random.value<0.5 then -1 else +1 end)*dbms_random.value*1.5,2) as y from dual connect by rownum<=30 union all --cluster 2 select 2 as cluster_number, ROUND(8+(case when dbms_random.value<0.5 then -1 else +1 end)*dbms_random.value*2,2) as x, ROUND(2+(case when dbms_random.value<0.5 then -1 else +1 end)*dbms_random.value*2,2) as y from dual connect by rownum<=40 union all --cluster 3 select 3 as cluster_number, ROUND(6+(case when dbms_random.value<0.5 then -1 else +1 end)*dbms_random.val...

This article just my first step in Spark ML. You can read the short description on wiki -  K-means_clustering First of all, we need to check everything. 1) Test data, our data is a set of points in 2D, each point has 3 properties: X coordinate, Y coordinate, and a color. I am going to make 2 clustering, without and with colors. I use Oracle SQL (because it's under the hand) to generate the test data. Special select 3 cluster centers and generate random drop table delit_test_data; create table delit_test_data as --cluster 1 select 1 as cluster_number, ROUND(2+(case when dbms_random.value<0.5 then -1 else +1 end)*dbms_random.value*1.5,2) as x, ROUND(2+(case when dbms_random.value<0.5 then -1 else +1 end)*dbms_random.value*1.5,2) as y from dual connect by rownum<=30 union all --cluster 2 select 2 as cluster_number, ROUND(8+(case when dbms_random.value<0.5 then -1 else +1 end)*dbms_random.value*2,2) as x, ROUND(2+(case when d...

In this little example, I show how to read and write Map columns in Cassandra. Cassandra table, here we have columns: ticker_id - ID of financial tool like EURUSD, ORCL, APPL, GOLD. ddate       - simple date of DAY. ('2019-01-25','2019-01-26',...) CREATE TABLE mts_bars.td_bars_3600( ticker_id int, ddate date, bar_1 map , bar_2 map , bar_3 map , bar_4 map , bar_5 map , bar_6 map , bar_7 map , bar_8 map , bar_9 map , bar_10 map , bar_11 map , bar_12 map , bar_13 map , bar_14 map , bar_15 map , bar_16 map , bar_17 map , bar_18 map , bar_19 map , bar_20 map , bar_21 map , bar_22 map , bar_23 map , bar_24 map , PRIMARY KEY ((ticker_id), ddate) ) WITH CLUSTERING ORDER BY (ddate DESC); Primary key contains 2 columns, ticker_id and ddate ticker_id - is a PARTITION KEY, is responsible for data distribution across your nodes. ddate      - is a Clustering Key, is responsible for...

HDFS directory preparation on Hadoop cluster. From LXC with name hdpnn execute next: Hadoop and Cassandra cluster installation you can find  in this article. My idea is writing an application with Scala which will be run on Spark cluster for load data from Cassandra into HDFS parquet files, for future analyzes with Hive. First I do it manually, step by step. 1) HDFS directory preparation [hadoop@hdpnn ~]$ hadoop fs -mkdir /user/tickers [hadoop@hdpnn ~]$ hadoop fs -rm -r /user/tickers/ticker_23 [hadoop@hdpnn ~]$ hdfs dfs -chown root:root /user/tickers [hadoop@hdpnn ~]$ hadoop fs -ls /user drwxr-xr-x - root root 0 2019-01-10 06:27 /user/tickers 2) Load data with Spark using spark-shell (on smn) spark-shell --driver-memory 1G --executor-memory 1G --driver-cores 1 --executor-cores 1 --jars "/opt/spark-2.3.2/jars/spark-cassandra-connector-assembly-2.3.2.jar" --conf spark.cassandra.connection.host= 192.168.122.192 --conf "spark.sql.parquet.writeL...

This short topic show full example of using Spark Windows with  UDAF 1) I have Cassandra cluster with column family that will be used as our source data 2) I want to calculate average "disp" by the window from current rows plus two next rows.    SparkWindow 3) Scala code to load source Dataset from Cassandra, I exclude some columns, take just part of data (ticker=1 and width=30) and sort it ascending by ts_begin (this is a unix_timestamp from the external system) def getBarsFromCass(TickerID :Int, BarWidthSec :Int) = { import org.apache.spark.sql.functions._ spark.read.format("org.apache.spark.sql.cassandra") .options(Map("table" -> "bars", "keyspace" -> "mts_bars")) .load() .where(col("ticker_id") === TickerID && col("bar_width_sec") === BarWidthSec ) .select(col("ts_begin"), col("btype"), col("disp...