Introduction Cloudera Data Warehouse architecture leverage compute/storage separation, this is different from the standard Hadoop architecture.     Figure 1: Cloudera Modern Data Warehouse Architecture   The objective of this post is to show how to import the wide functions and code material that we have on the open-source community inside Cloudera Data Warehouse using the object storage architecture. For this, we'll use ESRI Spatial Framework as an example. Prerequisites We'll use github to download the ESRI project, Java and maven to build the necessary JAR files. Step 1: Download the files from ESRI Github repository Download the necessary files from ESRI Spatial Framework Github repository, this can be done using the following command: $ git clone https://github.com/Esri/spatial-framework-for-hadoop.git Figure 2: Cloning ESRI project This will create a dir called "spatial-framework-for-hadoop", enter in this directory to build the project, and generate the JAR files that will be used for the functions. Step 2: Build the project using Maven To build the project using Apache Maven,   Install it from the Maven website and perform the installation according to your OS. Within the ESRI github project directory, you can perform the build using the following:  $ mvn package After a successful run you should see something like this: Figure 3: Building ESRI project Step 3: Copy the JAR files to the Cloudera Data Warehouse Object Storage After creating the JAR files containing the functions that will be used, copy them to the object storage that is being used. In this example, we're using AWS S3. You can use the same bucket that is being used by Cloudera Data Warehouse for External Data or add in another bucket. For more information, see Adding access to external S3 buckets for Cloudera Data Warehouse clusters on AWS. The build will create the JAR file that will be necessary to upload to the object storage: spatial-sdk-hive-2.1.1-SNAPSHOT.jar -> Located in <path/to/githubproject>/spatial-framework-for-hadoop/hive/target​ In my example, I've created a jars folder in my bucket and uploaded using the AWS S3 Console upload tool. Upload JAR in the object storage bucket: Figure 4: Upload JAR File into the object storage. File uploaded: Figure 5: JAR uploaded in the object storage. Step 4: Create the Functions Now that the JAR file is in the object storage, you need just to create the functions inside Cloudera Data Warehouse pointing to the JAR that is uploaded. In the Virtual Warehouse DAS or HUE you can use the following syntax to create the functions (this example creates the ST_Geometry function): CREATE FUNCTION ST_Geometry AS 'com.esri.hadoop.hive.ST_Geometry' USING JAR 's3a://<BucketName>/warehouse/tablespace/external/jars/spatial-sdk-hive-2.1.1-SNAPSHOT.jar'; For more CREATE FUNCTION statements for ESRI you can visit my Github link. Step 5: Test the Functions Now the functions are ready to be used. Run the following to  test if it's working submitting: SELECT ST_AsText(ST_Point(1, 2)); Figure 6: Functions working   Summary In this article we saw how easy it is to import/create the vast functions ecosystem in the open-source community inside Cloudera Data Warehouse, we used specifically the ESRI Spatial functions.   For more information on how to use ESRI functions in Cloudera Data Platform you can check Geo-spatial Queries with Hive using ESRI Geometry and Spatial Framework for Hadoop or Esri/gis-tools-for-hadoop. ... View more

Introduction   Cloudera Data Platform Base doesn't have one Quickstart/Sandbox VM like the ones for CDH/HDP releases that helped a lot of people (including me), to learn more about the open-source components and also see the improvements from the community in CDP Runtime.    The objective of this tutorial is to enable and create a VM from scratch via some automation (Shell Script and Cloudera Template) that can help whoever wants to use and/or learn Cloudera CDP in a Sandbox/Quickstart like environment in your machine.   Pre-Requisites   This exercise is performed on a Mac OS but you can install Vagrant/Virtualbox on Windows/Linux machines (https://www.vagrantup.com/docs/installation). The versions below were tested at the moment of writing this blog and may change in the future.   The machine needs to have at least: 80 GB of free disk space; 12 GB free RAM; 8 free VCPU; Good internet connection; Install Virtualbox and Vagrant   These are the software that we'll use to run our virtualized environment and to download and install Virtualbox and Vagrant execute the following commands in your host machine (For MAC OS):   For Mac $ brew cask install virtualbox $ brew cask install vagrant $ brew cask install vagrant-manager   The manager is optional and can be used to manage your Virtual Machines on the menu bar.   For Windows Download Virtualbox here and Vagrant here and install the files. Also, take a look at this instruction regarding hypervisor.   For Linux Follow Virtualbox and Vagrant instructions to install in your Linux Version.   Step 1: Vagrant Centos 7 Virtual Machine Setup with CDP   Download the Centos VM and the files necessary for set up in an empty folder. In this example, I'll download within the "~/cdpvm/" folder. Also, in your host machine execute the following commands:   $ cd ~ $ mkdir cdpvm $ cd cdpvm $ wget https://cloud.centos.org/centos/7/vagrant/x86_64/images/CentOS-7-x86_64-Vagrant-2004_01.VirtualBox.box $ wget https://raw.githubusercontent.com/carrossoni/CDPDCTrial/master/scripts/VMSetup.sh   Go to the folder that you've downloaded your VM file (cd ~/cdpvm) and initialize the Virtual Machine using the following command:   $ vagrant box add CentOS-7-x86_64-Vagrant-2004_01.VirtualBox.box --name centos7 $ vagrant plugin install vagrant-disksize $ vagrant init centos7​   After this step, you should have a file called "Vagrantfile" in the same directory, open the file with an editor (vim for example) and below the line  config.vm.box = "centos7" add the following:   config.vm.network "public_network" config.vm.network :forwarded_port, guest: 7180, host: 7180 config.vm.network :forwarded_port, guest: 8889, host: 8889 config.vm.network :forwarded_port, guest: 9870, host: 9870 config.vm.network :forwarded_port, guest: 6080, host: 6080 config.vm.network :forwarded_port, guest: 21050, host: 21050 config.vm.hostname = "localhost" config.disksize.size = "80GB" config.vm.provision "shell", path: "VMSetup.sh" config.vm.provider "virtualbox" do |vb| # Display the VirtualBox GUI when booting the machine vb.gui = true # Customize the amount of memory on the VM: vb.memory = "12024" vb.cpus = "8" end   Save the file and now we can init and bring up the VM:   $ vagrant up​   Now it'll ask to bridge to your public network (only for the first time) normally it's the one that you're connected on the internet, in my case is en0: After this, the VM will be provisioned and automated CDP process will start, this will take up to one hour depending on your connection since also it'll configure the VM and also install all the components for Cloudera Manager and the Services in an automated process located in https://github.com/carrossoni/CDPDCTrial/ The template and the cluster created at the end will contain the following services:   HUE HDFS Hive Metastore Impala Ranger Zookeeper​   After the install you can add more services like Nifi, Kafka etc. depending on the number of resources that you've reserved for the VM. After the execution you should see the exit below (this will take up about 30 min to one hour depending on your connection since it'll download all the packages and parcels necessary for provisioning CDP Runtime): After this the VM will reboot to do a fresh start, wait around 5 minutes for the services spin up and go to the next step. Troubleshooting: If the install process failed, likely it's a problem during the VM configuration if CM was installed you can try going to https://localhost:7180 directrly and finish the install process manually via Cloudera Manager UI To ssh there's two options, the easy one is to simple go to directory that the Vagrantfile is located (that you have used to perform the setup of the VM) and type:   $ vagrant ssh​     The other option is to configure your VM in the Virtualbox UI to attach a USB and copy the clouderakey.pem file that was created during the automation process. Then you are able to ssh the machine via "ssh -i clouderakey.pem vagrant@cloudera" After ssh using both scenarios you can sudo the box and start looking the machine, try to see if the hostname and ip in /etc/hosts is configured properly (most common issue since depends of your machine network). If after the template import you have an error message, cloudera manager can show what's happening, work in the error and then resume the import cluster template process in the running commands tab. If you are in this step now normally is a matter to view logs and/or see if there isn't resources available, at the end you can restart the cluster to see if it's something that was stuck. This is normal since we are working in a constrained environment.   Step 2: Cloudera Data Platform Access   After the automated process our CDP Runtime is ready (actually we've provisioned in only one step)! In your machine browser you can connect to the CM with the following URL:    http://localhost:7180     Password will be admin/admin after the first login you can choose the 60-day trial option and click in "Continue": The Welcome page appears, click in the Cloudera Logo on the top left since we've already added a new cluster with the automated process: At this point all the services are initiated, some errors may happen since we are working on a resource constraint environment, usually follow the logs that it'll be easy to see in Cloudera Manager what's happening, also you can suppress warning messages if it's not something critical. We've our environment ready to work and learn more about CDP!    HUE and Data Access   You can log in in Hue from the URL http://localhost:8889/hue and for the first time we will use the user admin/admin, this will be the admin user for HUE: For example, I'll upload data from the California COVID-19 Testing that I've downloaded to my machine. In HUE go on the left panel and choose "Importer" → Type = File, choose /user/admin directory and then click in "Upload a file", choose your file (statewide_testing.csv) and then "Open". Now click in the file that you've uploaded and this will go to the next step: Click in Next and HUE will infer the table name, field types etc, you can change or leave as is and click in "Submit": At the end you should see the success of the job, close the job status window, and click in the Query button: Now that we've hour data we can query and use Impala SQL in the data that we've uploaded!     (Optional) Ranger Security Masking with Impala Example   To start using/querying the environment with the system user/password that we've created (cloudera/cloudera)  first we need to enter in Ranger we need to allow access to this user, click in the Ranger service and then in Ranger Admin WebUI:  Now we have the initial Ranger screen. Login with the user/password admin/cloudera123: In the HADOOP SQL session click in the Hadoop SQL link. We will create a new policy to allow access to the new table but seeing the tested column in masked format with null results. For that click in the Masking tab and then Add New Policy with the following values: Click in the Add button and now go back to the Access tab and Add New Policy Button with the following parameters: Click in Add button and now our user should be ready to select only the data on this table with the masked values. First we'll configure the user in HUE, in the left panel click in the initial button and then in "Manage Users": Click in "Add User" and then in username put cloudera with the password cloudera, you can skip step 2 and 3 clicking directly in Add user. Logout from HUE and login with our new create user, go to the query editor and select the data again: You should see the masked policy in action!    Summary In this blog we've learned: How to Setup a Vagrant Centos 7 machine with Virtualbox and CDP Packages Configure CDP-DC for the first run Configure data access Setup simple security policies with the masking feature You can play with the services, install other parcels like Kafka/Nifi/Kudu to create a streaming ingestion pipeline, and query in real-time with Spark/Impala. Of course for that, you'll need more resources and this can be changed in the beginning during the VM Configuration. ... View more

Introduction How do we quickly gain insight and start working with data in a secure, governed, and scalable environment in the cloud?   This article explains how to achieve this using the Cloudera Data Warehouse platform connected with Apache Superset.   Cloudera Data Warehouse in CDP (Cloudera Data Platform) is an enterprise solution for modern analytics. It's an auto-scaling, highly concurrent, and cost-effective hybrid, a multi-cloud analytics solution that ingests data anywhere, at massive scale, from structured, unstructured, and edge sources.   Apache Superset (incubating) is a modern, enterprise-ready business intelligence web application. Pre-Requisites This exercise is performed on a Mac OS. The versions below were tested at the moment of writing this article and may change in the future:   Python         Python 3.7.5 pip 20.0.2         After Python/pip installation, install the following packages/versions in Python (we recommend using venv before this step):         impyla==0.16.1 thrift==0.13.0 thrift_sasl==0.2.1         Apache Superset Configuration Apache Superset can be installed on your machine or executed in a Docker environment. In this example, we will use the steps provided in Python Virtualenv and the version is:         apache-superset==0.999.0.dev0         After setting up the environment, you can access Superset UI with in the following address:         http://127.0.0.1:8088/         Figure 1: Welcome to Apache Superset The default username/password is admin/admin. Cloudera Data Warehouse If you don't have an Impala Virtual Warehouse (used in this example), you need to create one that will connect to the Database Catalog. This is a very simple step and can be done in minutes. Once you have created a virtual warehouse, if your Database Catalog already has the Tables, Security, and Metadata Definitions to be used, you or the user/application (in our case Apache Superset) can start using the platform. More information can be obtained in this link. Figure 2: Cloudera Data Warehouse   Here, we will be using the "default-impala" Virtual Warehouse. Since the environment is not running and nobody is using it, it is not consuming any resources. After the Virtual Warehouse creation, you will need to collect the URL to connect to your environment like the following example: Figure 3: Getting Access URL in Cloudera Data Warehouse Once you save the access URL, you can configure the Dashboard in Apache Superset. Configure Cloudera Data Warehouse as Source Database After the prerequisites, we'll configure the connection in Apache Superset. To start creating the dashboard in Cloudera Data Warehouse, perform the following Click Source > Database in the top left menu:                                                 Figure 4: Configuring Source Database On the top right corner click in the "Add new record" button:                                                   Figure 5: Add new database button Now, we need to put the configuration in the following screen:                                                       Figure 6: Configuring Database   jdbc:impala://example-default-impala.env-pkXXXX.dwx.example.site:443/default;AuthMech=3;transportMode=http;httpPath=cliservice;ssl=1;UID=luizcarrossoni;PWD=PASSWORDTo:impala://example-default-impala.env-pkXXXX.dwx.example.site:443/default?auth_mechanism=PLAIN&http_path=cliservice&use_http_transport=True&use_ssl=TrueExpose in SQL Lab: CheckedAllow Multi Schema Metadata Fetch: CheckedExtra: Here, we'll pass our Cloudera Data Platform access credentials, there are other ways to do this that are more secure in Apache Superset:{ "metadata_params": {}, "engine_params": { "connect_args": { "user" : "<cdpuser>", "password" : "<password>" } } } Database Name: Choose a name for example "ClouderaDataPlatform" SQLAlchemy URI:  We'll use the Access URL that we got in Cloudera Console, we need to customize the URI in order to use impyla and the URL supported by SQLAlchemy: From:   After providing the config information, click the Test button in the SQLAlchemy URI Field, to see if everything is working properly. If the Virtual Warehouse is in Stopped state, it'll first start the Warehouse and then you'll see that the test was successful:                                                      Figure 7: Starting Virtual Warehouse                                                 Figure 8: Connection Successful Now you can save the connection and start creating your dashboards. Query Data through SQL Lab You can query the data in the Virtual Warehouse using SQL Lab in Superset: Figure 9: Query Data in SQL Lab Note: Since the table is querying the data that supposedly has PII information (ccnumber), the data comes as hashes. This is because we have the following policy in place for the user:    Figure 10: Masking Policy Create your Dashboard To create the Dashboard using Apache Superset in Cloudera Data Platform, do the following: Add the table as a source in the following menu:                                                          Figure 11: Add Table Source Add the ww_customers_data table to start creating the dashboard:                                                      Figure 12: Create Source Table Create Charts using the source table that is created and use the charts in a Dashboard:   ... View more