MySQL 8 for big data : effective data processing with MySQL 8, Hadoop, NoSQL APIs, and other big data tools / Shabbir Challawala [and three others].

Format:

Book

Author/Creator:

Challawala, Shabbir, author.

Language:

English

Subjects (All):

MySQL (Electronic resource).

Apache Hadoop.

Big data.

Physical Description:

1 online resource (291 pages) : illustrations (some color)

Edition:

1st ed.

Place of Publication:

Birmingham, England ; Mumbai, [India] : Packt, 2017.

Biography/History:

Lakhatariya Jaydip: Jaydip Lakhatariya has rich experience in portal and J2EE frameworks. He adapts quickly to any new technology and has a keen desire for constant improvement. Currently, Jaydip is associated with a leading open source enterprise development company, KNOWARTH Technologies, where he is engaged in various enterprise projects. Jaydip, a full-stack developer, has proven his versatility by adopting technologies such as Liferay, Java, Spring, Struts, Hadoop, MySQL, Elasticsearch, Cassandra, MongoDB, Jenkins, SCM, PostgreSQL, and many more. He has been recognized with awards such as Merit, Commitment to Service, and also as a Star Performer. He loves mentoring people and has been delivering training for Portals and J2EE frameworks. Mehta Chintan: Chintan Mehta is a co-founder of KNOWARTH Technologies and heads the cloud/RIMS/DevOps team. He has rich, progressive experience in server administration of Linux, AWS Cloud, DevOps, RIMS, and on open source technologies. He is also an AWS Certified Solutions Architect. Chintan has authored MySQL 8 for Big Data, Mastering Apache Solr 7. x, MySQL 8 Administrator's Guide, and Hadoop Backup and Recovery Solutions. Also, he has reviewed Liferay Portal Performance Best Practices and Building Serverless Web Applications. Challawala Shabbir: Shabbir Challawala has over 8 years of rich experience in providing solutions based on MySQL and PHP technologies. He is currently working with KNOWARTH Technologies. He has worked in various PHP-based e-commerce solutions and learning portals for enterprises. He has worked on different PHP-based frameworks, such as Magento E-commerce, Drupal CMS, and Laravel. Shabbir has been involved in various enterprise solutions at different phases, such as architecture design, database optimization, and performance tuning. He has been carrying good exposure of Software Development Life Cycle process thoroughly. He has worked on integrating Big Data technologies such as MongoDB and Elasticsearch with a PHP-based framework. Patel Kandarp: Kandarp Patel leads PHP practices at KNOWARTH Technologies. He has vast experience in providing end-to-end solutions in CMS, LMS, WCM, and e-commerce, along with various integrations for enterprise customers. He has over 9 years of rich experience in providing solutions in MySQL, MongoDB, and PHP-based frameworks. Kandarp is also a certified MongoDB and Magento developer. Kandarp has experience in various Enterprise Application development phases of the Software Development Life Cycle and has played prominent role in requirement gathering, architecture design, database design, application development, performance tuning, and CD/CI. Kandarp has a Bachelor of Engineering in Information Technology from a reputed university in India.

Contents:

Cover

Copyright

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Customer Feedback

Table of Contents

Preface

Chapter 1: Introduction to Big Data and MySQL 8

The importance of Big Data

Social media

Politics

Science and research

Power and energy

Fraud detection

Healthcare

Business mapping

The life cycle of Big Data

Volume

Variety

Velocity

Veracity

Phases of the Big Data life cycle

Collect

Store

Analyze

Governance

Structured databases

Basics of MySQL

MySQL as a relational database management system

Licensing

Reliability and scalability

Platform compatibility

Releases

New features in MySQL 8

Transactional data dictionary

Roles

InnoDB auto increment

Supporting invisible indexes

Improving descending indexes

SET PERSIST

Expanded GIS support

The default character set

Extended bit-wise operations

InnoDB Memcached

NOWAIT and SKIP LOCKED

Benefits of using MySQL

Security

Scalability

An open source relational database management system

High performance

High availability

Cross-platform capabilities

Installing MySQL 8

Obtaining MySQL 8

MySQL 8 installation

MySQL service commands

Evolution of MySQL for Big Data

Acquiring data in MySQL

Organizing data in Hadoop

Analyzing data

Results of analysis

Summary

Chapter 2: Data Query Techniques in MySQL 8

Overview of SQL

Database storage engines and types

InnoDB

Important notes about InnoDB

MyISAM

Important notes about MyISAM tables

Memory

Archive

Blackhole

CSV

Merge

Federated

NDB cluster

Select statement in MySQL 8

WHERE clause

Equal To and Not Equal To

Greater than and Less than

LIKE

IN/NOT IN

BETWEEN

ORDER BY clause

LIMIT clause

SQL JOINS.

INNER JOIN

LEFT JOIN

RIGHT JOIN

CROSS JOIN

UNION

Subquery

Optimizing SELECT statements

Insert, replace, and update statements in MySQL 8

Insert

Update

Replace

Transactions in MySQL 8

Aggregating data in MySQL 8

The importance of aggregate functions

GROUP BY clause

HAVING clause

Minimum

Maximum

Average

Count

Sum

JSON

JSON_OBJECTAGG

JSON_ARRAYAGG

Chapter 3: Indexing your data for High-Performing Queries

MySQL indexing

Index structures

Bitmap indexes

Sparse indexes

Dense indexes

B-Tree indexes

Hash indexes

Creating or dropping indexes

UNIQUE | FULLTEXT | SPATIAL

Index_col_name

Index_options

KEY_BLOCK_SIZE

With Parser

COMMENT

VISIBILITY

index_type

algorithm_option

lock_option

When to avoid indexing

MySQL 8 index types

Defining a primary index

Primary indexes

Natural keys versus surrogate keys

Unique keys

Defining a column index

Composite indexes in MySQL 8

Covering index

Invisible indexes

Descending indexes

Defining a foreign key in the MySQL table

RESTRICT

CASCADE

SET NULL

NO ACTION

SET DEFAULT

Dropping foreign keys

Full-text indexing

Natural language fulltext search on InnoDB and MyISAM

Fulltext indexing on InnoDB

Fulltext search in Boolean mode

Differentiating full-text indexing and like queries

Spatial indexes

Indexing JSON data

Generated columns

Virtual generated columns

Stored generated columns

Defining indexes on JSON

Chapter 4: Using Memcached with MySQL 8

Overview of Memcached

Setting up Memcached

Installation

Verification

Using of Memcached

Performance tuner

Caching tool

Easy to use

Analyzing data stored in Memcached

Memcached replication configuration.

Memcached APIs for different technologies

Memcached with Java

Memcached with PHP

Memcached with Ruby

Memcached with Python

Chapter 5: Partitioning High Volume Data

Partitioning in MySQL 8

What is partitioning?

Partitioning types

Horizontal partitioning

Vertical partitioning

Horizontal partitioning in MySQL 8

Range partitioning

List partitioning

Hash partitioning

Column partitioning

Range column partitioning

List column partitioning

Key partitioning

Sub partitioning

Splitting data into multiple tables

Data normalization

First normal form

Second normal form

Third normal form

Boyce-Codd normal form

Fourth normal form

Fifth normal form

Pruning partitions in MySQL

Pruning with list partitioning

Pruning with key partitioning

Querying on partitioned data

DELETE query with the partition option

UPDATE query with the partition option

INSERT query with the partition option

Chapter 6: Replication for building highly available solutions

MySQL replication

MySQL cluster

Oracle MySQL cloud service

MySQL with the Solaris cluster

Replication with MySQL

Benefits of replication in MySQL 8

Scalable applications

Secure architecture

Large data analysis

Geographical data sharing

Methods of replication in MySQL 8

Replication using binary logs

Replication using global transaction identifiers

Replication configuration

Replication with binary log file

Replication master configuration

Replication slave configuration

Replication with GTIDs

Global transaction identifiers

The gtid_executed table

GTID master's side configurations

GTID slave's side configurations

MySQL multi-source replication

Multi-source replication configuration.

Statement-based versus row-based replication

Group replication

Requirements for group replication

Group replication configuration

Group replication settings

Choosing a single master or multi-master

Host-specific configuration settings

Configuring a Replication User and enabling the Group Replication Plugin

Starting group replication

Bootstrap node

Chapter 7: MySQL 8 Best Practices

MySQL benchmarks and configurations

Resource utilization

Stretch your timelines of benchmarks

Replicating production settings

Consistency of throughput and latency

Sysbench can do more

Virtualization world

Concurrency

Hidden workloads

Nerves of your query

Benchmarks

Best practices for MySQL queries

Data types

Not null

Indexing

Search fields index

Data types and joins

Compound index

Shorten up primary keys

Index everything

Fetch all data

Application does the job

Existence of data

Limit yourself

Analyze slow queries

Query cost

Best practices for the Memcached configuration

Resource allocation

Operating system architecture

Default configurations

Max object size

Backlog queue limit

Large pages support

Sensitive data

Restrict exposure

Failover

Namespaces

Caching mechanism

Memcached general statistics

Best practices for replication

Throughput in group replication

Infrastructure sizing

Constant throughput

Contradictory workloads

Write scalability

Chapter 8: NoSQL API for Integrating with Big Data Solutions

NoSQL overview

Changing rapidly over time

Scaling

Less management

Best for big data

NoSQL versus SQL

Implementing NoSQL APIs

NoSQL with the Memcached API layer

Prerequisites

NoSQL API with Java

NoSQL API with PHP

NoSQL API with Python

NoSQL API with Perl.

NDB Cluster API

NDB API for NodeJS

NDB API for Java

NDB API with C++

Chapter 9: Case study: Part I - Apache Sqoop for exchanging data between MySQL and Hadoop

Case study for log analysis

Using MySQL 8 and Hadoop for analyzing log

Apache Sqoop overview

Integrating Apache Sqoop with MySQL and Hadoop

Hadoop

MapReduce

Hadoop distributed file system

YARN

Setting up Hadoop on Linux

Installing Apache Sqoop

Configuring MySQL connector

Importing unstructured data to Hadoop HDFS from MySQL

Sqoop import for fetching data from MySQL 8

Incremental imports using Sqoop

Loading structured data to MySQL using Apache Sqoop

Sqoop export for storing structured data from MySQL 8

Sqoop saved jobs

Chapter 10: Case study: Part II - Real time event processing using MySQL applier

Case study overview

MySQL Applier

SQL Dump and Import

Sqoop

Tungsten replicator

Apache Kafka

Talend

Dell Shareplex

Comparison of Tools

MySQL Applier overview

MySQL Applier installation

libhdfs

cmake

gcc

FindHDFS.cmake

Hive

Real-time integration with MySQL Applier

Organizing and analyzing data in Hadoop

Index.

Notes:

Includes index.

Description based on online resource; title from PDF title page (ebrary, viewed November 22, 2017).

ISBN:

1-78839-042-3

OCLC:

1007846497