CLASSIFICATION COMPLEX QUERY SQL FOR DATA LAKE MANAGEMENT USING MACHINE LEARNING

A query is a request for data or information from a database table or a combination of tables. It allows for a more accurate database search. SQL queries are divided into two types, namely, simple queries and complex queries. Complex SQL is the use of SQL queries that go beyond standard SQL by using the SELECT and WHERE commands. Complex SQL queries often involve the use of complex joins and subqueries, where the queries are nested in a WHERE clause. Complex SQL queries can be grouped into two types of queries, namely, Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) queries. In the implementation of complex SQL queries in the NoSQL database, a classification process is needed due to the varying data formats, namely, structured, semi-structured, and unstructured data. The classification process aims to make it easier for the query data to be organized by type of query. The classification method used in this research is the Naive Bayes Classifier (NBC) which is generally often used in text data, and the Support Vector Machine (SVM), which is known to work very well on data with large dimensions. The two methods will be compared to determine the best classification result. The results showed that SVM was 84.61% accurate in terms of classification, and comparatively, NBC was at 76.92%.

Auto-pipeline

Recent work has made significant progress in helping users to automate single data preparation steps, such as string-transformations and table-manipulation operators (e.g., Join, GroupBy, Pivot, etc.). We in this work propose to automate multiple such steps end-to-end, by synthesizing complex data-pipelines with both string-transformations and table-manipulation operators. We propose a novel by-target paradigm that allows users to easily specify the desired pipeline, which is a significant departure from the traditional by-example paradigm. Using by-target, users would provide input tables (e.g., csv or json files), and point us to a "target table" (e.g., an existing database table or BI dashboard) to demonstrate how the output from the desired pipeline would schematically "look like". While the problem is seemingly under-specified, our unique insight is that implicit table constraints such as FDs and keys can be exploited to significantly constrain the space and make the problem tractable. We develop an AUTO-PIPELINE system that learns to synthesize pipelines using deep reinforcement-learning (DRL) and search. Experiments using a benchmark of 700 real pipelines crawled from GitHub and commercial vendors suggest that AUTO-PIPELINE can successfully synthesize around 70% of complex pipelines with up to 10 steps.

Forecast of cattle leukemia virus epizootic development in West Kazakhstan region

Bovine leukemia is a chronic viral infectious disease that is widespread in many countries of the world, including the Republic of Kazakhstan. Based on our spectral and combinatorial analysis, this paper presents the results of supposed scenarios of leukemia infection development in the territory of West Kazakhstan region, where there is the alternating change of ups and downs virus level having a direct dependence on solar activity. To solve this problem, we used previously developed computer programs to decompose the dynamics of the cattle infection rate in the West Kazakhstan region for 2006-2016 into a Fourier series, then generate all possible combinations, create a database table with forecast values for several years and select the best numerical series. As a result of this research, it was noted that the computer mathematical model simulating the dynamics of bovine leukemia virus (BLV) carrier, based on the properties of the trigonometric Fourier polynomial, has 100% coincidence with the actual trend. Then in order to identify trends that would maximally reflect the real trend, we conducted a combinatorial analysis of the spectral model which allowed us to increase the number of alternative scenarios for the development of BLV infection for the next five years (2021-2026). According to the above mentioned mathematical model, based on combinatorial analysis, in 2022 the forecast of infectability corridor will average from 4.15 to 11.01%; in 2023 from 3.89 to 9.76%; in 2024 from 8.04 to 16.47%; in 2025 from 6.86 to 15.23% and in 2026 from 3.98 to 8.79%.

An approach to information export from database for complex master-detail table hierarchies into a single flat table form

When developing a database client application it is usually required to implement a capability to export information from database tables into some simple data-exchange representations of tables like the CSV (Comma-Separated Values) format. It is very straightforward to implement the export for a single database table. But some information about the records of a master table may be represented by the records from, sometimes, several detail tables, so the resulting CSV table would be incomplete without this information. We develop AIS (automated information systems) using declarative specifications of database applications (SDA). The AIS’es are implemented using general algorithms, which are directed by the specifications. In this article we’ll consider the approach to generation of flat tables from the master-detail groups of tables, which allows users to represent compactly the data from a hierarchy of tables related by the master-detail relationships and to select conveniently which kind of information to include into the resulting table.

Experience of Developing a database of storytellers of the Yakut Epos

The results of the analysis of the basic principles of compiling the database “Olonkhosuts of Yakutia of the 17th—20th centuries” — about the storytellers of the Yakut national epic are presented. The relevance of the research is determined by some difficulties in the systematization and design of the information content of the repository. A brief overview of the history of the study of the personalities of Yakut storytellers is given, the stages of database development are discussed in detail. It is noted that as a result of the generalization of the material a reference book that contains more than 1300 names of storytellers of the 17th-20th centuries was published by the team of the Olonkho Research Institute of M. K. Ammosov North-Eastern Federal University. Problems identified in the process of filling in the cells are described. Particular attention is paid to entering data in the national language, their transliteration and translation into Russian. The definition of the types of the second names of olonkhosuts is given, the expediency of allocating a separate field for nicknames-pseudonyms reflecting the status and popular recognition of the talent of the narrator is substantiated. A sample database table on olonkhosuts is presented. As a result of the study, the parameters for selecting data for inclusion in the repository were compiled, the need for additional research work to identify the facts of the change of residence of olonkhosuts was determined, and recommendations were proposed for improving the principles of information content of the database.

SQLite Sebagai Pengganti Lucene.Net pada Pencarian Produk Toko Online

A business institution can promote their products in its online shop. With the promotion of online stores, consumers can find out which products are sold at the store. Consumers who are looking for a product can search based on various attributes of goods that are scattered in various fields in the database table, and can even be spread across different tables. All attributes that point to an item are collected in one document which will be searched using the Full Text Search system. Two alternatives to the Full Text Search system were selected; Lucene.Net and Sqlite Full Text Search. Before actually being used, these two search system alternatives were tested first. In document storage size, Lucene.Net is superior by 6.78 times. The speed of writing Sqlite search documents is superior by between 1,875 times to 5,197 times. In terms of key search speed, Lucene.Net was superior by between 1,169 and 1,698 times. Based on the consideration of the speed and development of Lucene.Net Core which is still in beta stage, Sqlite Full Text Search is suitable for use in the product search process in the Online Store.

Minimal Structure of the Database for Storing Organisms’ Biodiversity Data

To date, a huge amount of data on organisms diversity has been accumulated. Databases help to store and use these data for scientific purposes. There exists several dozens of databases for storing biodiversity data that were described in publications. Each has an original structure which badly correlates with the structures of other databases. This complicates data exchange and the formation of big biodiversity data array. The cause of this situation is the lack of the formal definitions of universal data components, which allow to build the database with any data on the diversity of organisms. The analysis of publications and author’s experience show that such universal components are present in the characteristics of any organisms. For example, it is an organism taxonomic name and a location where it was found. There are six such components and they answer to one of the six questions: what, where, when, who, where from and where to. What determines the name of an organism; where determines the location where it was found; when indicates the date of finding; who enumerates the persons, who found and analyzed an organism; where from refers to publications, where data about an organism are extracted or published; where to shows in which biological collection an organism is put in. Each component corresponds to a separate database table. These tables are linked to the table with data about organism (individual) and they are not linked with each other. Attributes of the links between the organism table and the component tables are stored in intermediate tables. They are used, for example, to store bibliographic facts, descriptions of collection items or geographical points. They also act as docking stations to which tables with any other information are attached. The creation of any database about the diversity of living organisms begins with the definition of the table of organism specimens. It must be used even if there is no explicit data on organisms. In that case virtual organisms should be introduced and the other components should be linked with them by means of intermediate tables. The latter are docked to other data. Minimal structures of all the tables, links between them and examples of databases construction are described in the work.

Comparison of NoSQL Database Performance with SQL Server Database on Online Airplane Ticket Booking

<em>Flight ticket booking services have become more advanced, where bookings can be made through the android / iOS application and through a web browser, ticket reservations, no longer have to come to travel agents or come to the airport to book plane tickets. In this study using an online ticket booking database where one database uses the NoSQL database and another database uses SQL Server. The purpose of this research is to test the performance of NoSQL speed with SQL Server with the Insert, Delete and Select commands. The testing method uses 100 records, 500 records, 1000 records, and 5000 records, with each record being tested four times and then taken on average. The results of this study are that the NoSQL database Insert command has a speed 4 times faster than the SQL Server database for under 500 records, whereas above 500 NoSQL database records 5 times slower, the Delete NoSQL database command has a speed 3 times faster than the SQL database Server, and the command Select 1 NoSQL database table 55 times faster than SQL Server databases, while 2 NoSQL database tables are 18 times slower than SQL Server databases, while 3 NoSQL database tables are 10 times slower than SQL Server databases, whereas 4 database tables NoSQL is 16 times slower than SQL Server databases.</em>

Exterior Artificial Pancreas Project

Objective: Pump size exterior artificial pancreas that will keep the glucose between 120 – 150mg/dl. Method: The whole project based on the idea that we should already know one insulin unit how much is able to go down the glucose and one glucagons unit how much is able to increase the glucose. Less than 120 mg/dl it uses glucagons. More than 150 mg/dl it uses insulin. The pump checks the glucose automatically for every 8 minutes. The pump (which is software decision) will choose between insulin or glucagons base in an internal database table with prerequisite glucose values and the insulin or glucagons units requiring for each glucose value (adaptive database table for each diabetic). The pump (the software) is able to choose how many insulin or glucagon units it should use (that is not based on what the diabetic will eat, but base on the current glucose level that received from the sensor which is located in the human body, needle and sensor are one piece). The insulin should have a work duration of 8 minutes and works instantly. Result: I choose 120 mg/dl as the lowest allowance glucose level as this level is secure for the diabetic (there is a time to prevent big hypoglycemia). Conclusion: This project offers to diabetics insulin injections freedom, hypoglycemia prevention, run emergency tests, ideally for all ages, endocrinologists will have the software to adapt the internal database table of the pump for each diabetic needs.

Implementation of Attribute Based Symmetric Encryption through Vertically Partitioned Data in PPDM

The aim of this research article is to implement the symmetric encryption techniques on the vertically partitioned database table. In privacy-preserving data mining mainly sensitive attributes to be protected so that privacy will maintain during data mining. In this proposed algorithm the datasets partitioned in the three different sets. Then for each table for the selected attributes only one symmetrical encryption as well as different symmetric encryption is implemented. Initial data size, encryption execution time and data size after encryption are observed for each file of different data size. In result and analysis examined the performance of execution time and memory occupied after each encryption techniques is discussed and found that hybrid algorithm of using different symmetric encryption for each partitioned table is good as compared to implementing only one encryption on each partitioned table. This algorithm is mainly useful to provide privacy in PPDM in case of distributed data base of health care organizations.