Creating External Table for HDFS using Oracle Connector for Hadoop (OSCH)

Introduction

Oracle Big Data Connectors facilitate data access to data stored in an Apache Hadoop cluster. It can be licensed for use on either Oracle Big Data Appliance or a Hadoop cluster running on commodity hardware. There are three connectors available from which we are going to work on Oracle SQL Connector for Hadoop Distributed File System for the purpose of this post.

Connect Oracle SQL Developer to Hive

As Oracle SQL Developer is one of the most common SQL client tool used by Developers, Data Analyst and Data Architects to interact with Oracle and other relational systems. So extending the functionality of SQL developer to connect to hive is very useful for Oracle users. You can use the SQL Worksheet to query, create and alter Hive tables dynamically accessing data sources defined in the Hive metastore.

Using Hadoop Compression

Hadoop Compression

Hive can read data from a variety of sources, such as text files, sequence files, or even custom formats using Hadoop’s InputFormat APIs as well as can write data to various formats using OutputFormat API. You can take the leverage from Hadoop to store data as compressed to save significant disk storage. Compression also can increase throughput and performance. Compressing and decompressing data incurs extra CPU overhead, however, the I/O savings resulting from moving fewer bytes into memory can result in a net performance gain.

Hive for Oracle Developers and DBAs - Part III

Today we will discuss some more topic in Hive like Hive Queries, Distributed clauses, Sampling Data, Views,  Indexes and schema design. You can review the related posts below.

Hive for Oracle Developers and DBAs - Part  I

Hive for Oracle Developers and DBAs - Part II

Hive for Oracle Developers and DBAs - Part II

In the first Hive post  we have discussed the basic usage and functionality of Hive , today we move forward and discuss some advance functionality. I'll cover Collection, Tables and Partitions in this post.

Hive Installation and Configuration

What is Hive?

Apache Hive (originated in Facebook) is a Data warehouse system which is built to work on Hadoop to manage large datasets residing in HDFS. Hive is designed to enable easy data summarization, ad-hoc querying and analysis of large volumes of data using SQL At the same time, Hive's SQL gives users multiple places to integrate their own functionality to do custom analysis, such as User Defined Functions (UDFs). It is not designed for online transaction processing and best used for traditional data warehousing tasks.

Hive for Oracle Developers and DBAs - Part I

The Hadoop ecosystem emerged as a cost-effective way of working with large data sets. It imposes a particular programming model, called MapReduce, for breaking up computation tasks into units that can be distributed around a cluster of commodity, server class hardware, thereby providing cost-effective, horizontal scalability.

Hadoop Administration: Accessing HDFS (File system & Shell Commands)

You can access HDFS in many different ways. HDFS provides a native Java application programming interface (API) and a native C-language wrapper for the Java API. In addition, you can use a web browser to browse HDFS files. I'll be using CLI only in this post.

Hadoop Ecosystem - Quick Introduction

This is data age, data data everywhere. Although we cannot measure total volume of data stored electronically but it is estimated that 4.4 zettabytes in 2013 and is forecasting a tenfold growth by 2020 to 44 zettabytes. Clearly we can say this is Zettabyte Era. A zettabyte is equal to one thousand exabytes, one million petabytes, or one billion terabytes.

Big Data - The Bigger Picture

I’ve put the title with "The Bigger Picture" instead of "The Big Picture" because even big picture comes with much more details. The aim of this post is to provide a broad understanding of the topic without indulging into deeper details.  

Using Secure External Password Store - 11gR2

As all DBAs use some sort of shell scripts and have connections to the database which can be a major security issue if these scripts contain the database connection/passowrd details.

Instead of having these passwords in scripts, you can store password credentials for connecting to databases by using a client-side Oracle wallet. An Oracle wallet is a secure software container that stores authentication and signing credentials.

Proxy Authentication (Oracle DB)

Sometimes administrators need to connect to an application schema to perform maintenance. Sharing the application schema password among several administrators would provide no accountability. Instead, proxy authentication allows the administrators to authenticate with their own credentials first and then proxy to the application schema. In such cases, the audit records show the actual user who performed the maintenance activities. This form of proxy authentication is supported in Oracle Call Interface (OCI), JDBC, and on the SQL*PLUS command line.

Client-side Oracle wallet

Users are expected to provide the password when they connect to the database, but applications, middle-tier systems, and batch jobs cannot depend on a human to type the password. Earlier, a common way to provide passwords was to embed user names and passwords in the code or in scripts. This increased the attack surface and people had to make sure that their scripts were not exposed to anyone else. Also, if passwords were ever changed, changes to the scripts were required. Now you can store password credentials by using a client-side Oracle wallet. This reduces risks because the passwords are no longer exposed on command-line history, and password management policies are more easily enforced without changing application code whenever user names or passwords change.

12c: Zero App downtime using EBR

EBR, Edition-based redefinition allows multiple versions of PL/SQL objects, views and synonyms in a single schema, which makes it possible to upgrade the database component of an application while it is in use. An edition (nonschema object) is effectively a version label that can be assigned to all editionable objects in a schema. In Oracle 11gR2, if editioning was enabled for a user and you created a new object that was an editionable object in that user's schema, the object you created was automatically editionable. It was not possible to create non-editionable objects of one of these types in an edition enabled schema.

12c: Managing PDBs

Managing a pluggable database (PDB) involves a subset of the tasks required to administer a non-CDB. In this subset of tasks, most are the same for a PDB and a non-CDB, but there are some differences. A PDB administrator is limited to managing a single PDB and cannot affect other PDBs in the multitenant container database (CDB).

12c: ORA-65114: space usage in container is too high

ORA-65114: space usage in container is too high

Cause: Space usage in the current container exceeded the value of MAX_PDB_STORAGE for the container.

Action: Specify a higher value for MAX_PDB_STORAGE using the ALTER PLUGGABLE DATABASE statement.

12c: Network Data Encryption

Few organizations today have access to truly private and secure networks; instead, they share network infrastructure with other organizations. As a result, information traveling over these public or virtual private networks is often vulnerable to interception. The purpose of a secure cryptosystem is to convert plaintext data into unintelligible ciphertext based on a key, in such a way that it is very hard (computationally infeasible) to convert ciphertext back into its corresponding plaintext without knowledge of the correct key.

12c: Transparent Sensitive Data Protection

TSDP is the another new security feature of Oracle 12c , basically a complement to Data Redaction and Virtual Private Database. It allows to categorize sensitive columns across database and then to set a policy to each defined category of columns for example telephone number columns' policy to redact into a random number.

The packages DBMS_TSDP_MANAGE and DBMS_ TSDP_PROTECT are used to manage the categories and policies.

12c: Data Redaction

Oracle Data Redaction  is the feature which enables to mask data at run time that is returned from queries issued by applications. During the time that the data is being masked or redacted, all of the data processing is performed normally, and the back-end referential integrity constraints are preserved. It is ideal for situations in which you must redact specific characters out of the result set of queries of Personally Identifiable Information (PII) returned to certain application users. 

12c: Using Transparent Data Encryption

Oracle Database uses authentication, authorization, and auditing mechanisms to secure data in the database, but not in the operating system data files where data is stored. To protect these data files, Oracle Database provides Transparent Data Encryption (TDE). TDE encrypts sensitive data stored in data files. To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database, called a keystore.