Holowczak.com Tutorials Database and Programming tutorials

Time 2021-06-02 16:16:03

Web Name: Holowczak.com Tutorials Database and Programming tutorials

WebSite: http://holowczak.com

ID:180410

Keywords:

Tutorials,com,Holowczak,

Description:

Posted on June 1, 2021 by holowczak Getting started with PySpark on Google Cloud Platform Dataproc IntroductionThe purpose of this tutorial is to demonstrate the PySpark python interface to Spark running on the Google Cloud Platform service called Dataproc.Google Cloud Dataproc is Google s implementation of the Hadoop ecosystem that includes the Hadoop Distributed File System (HDFS) and Map/Reduce processing framework. The Google Cloud Dataproc system includes a number of applications such as Hive, Mahout, Pig, Spark and Hue that are built on top of Hadoop.Apache Spark is a processing framework that operates on top of the Hadoop Distributed File System (HDFS) (as well as other data stores). It features interactive shells that can launch distributed process jobs across a cluster. Spark supports programming language interfaces for Scala, Java, Python and SQL. PySpark is the Python interface to Spark.PrerequisitesBefore starting this tutorial, the following tutorials and notes should be reviewed.Basic familiarity with the Hadoop Ecosystem is required to get the most out of this tutorial. My Introduction to Hadoop notes – Including Spark can help with a review of these concepts.This tutorial assumes you already have a Google Cloud Platform account set up and funded. If you do not have a Google Cloud Platform account set up, please follow these instructions first.Topics OutlineProcessing a data set using Spark on Google Cloud Dataproc requires the following main steps:Enable the Google Cloud Compute Engine APICreate, Configure and Launch a Google Cloud Dataproc clusterLog in to the Hadoop cluster master nodeLoad data to the HDFSRun the Command Line Interface and issue PySpark commands to process the dataShut down cluster and remove any temporary resourcesEach of these topics will be covered in the sections that follow.Pages: 1 2 3 4 5 6 7 8 9 Category: Database Posted on May 14, 2020June 11, 2020 by holowczak Connecting to Oracle Cloud from Tableau Desktop using Oracle Instant Client IntroductionThe Oracle Cloud services include powerful managed transaction processing and data warehousing databases that can be deployed and scaled on demand. Tableau Desktop is a popular visualization tool used by many organizations to build reports and dashboards. While Tableau has always had the ability to make connections to local Oracle database servers, connecting to the Oracle Cloud requires some additional configuration steps to support the use of Oracle Cloud Wallet for secure communications between client and server.The three main components of this architecture are shown below:RequirementsCurrently (May, 2020) this configuration is only supported on the Windows operating system. The following software requirements and services are necessary to connect Tableau to Oracle Cloud:Windows 10 Operating SystemTableau Desktop for Windows version 2019.2 or newer. Version 2020.2 is used in this tutorial. Ensure you have installed and licensed Tableau Desktop before starting this tutorial. You will also need to install the Oracle database driver for Tableau.Oracle Instant Client version 12.1.0.2 or newer. Version 19.6 is used in this tutorial. Note: If you alreayd have an Oracle DBMS or Oracle Client installed, you may not need the instant client.Oracle Autonomous Data Warehouse or Autonomous Transaction Processing database instance running in the Oracle Cloud. An Oracle Autonomous Data Warehouse using Oracle 18c is used in this tutorial. Before starting this tutorial, ensure you have access to (or have created) an Autonomous Data Warehouse (ADW) instance or Autonomous Transaction processing (ATP) database instance in Oracle Cloud.Oracle Cloud Wallet (wallet.zip file) associated with the ADW or ATP database instance. Instructions for downloading the Wallet are given later in the tutorial. The next page covers downloading and installing the Oracle Instant Client.Pages: 1 2 3 4 5 6 7 8 Categories: Cloud Computing, Data Warehousing and Analytics Posted on March 19, 2020March 19, 2020 by holowczak Excel XMLA Interface to Pentaho Server IntroductionXML For Analysis (XML/A or XMLA) is an industry-standard language for discovering and using analytical data resources. The Web services architecture using XML and SOAP are the foundation for XML/A. To work with XML/A, a client BI application exchanges XML/A messages with the XML/A interface available on a data source such as Microsoft Azure and SQL Server Analysis Services, Oracle (Hyperion) Essbase or Pentaho Mondrian. The XMLA specification is defined here.In this demonstration, Microsoft Excel will be used as the client and the Pentaho Mondrian database will be used as the server. Pentaho BI Server comes with the SteelWheels OLAP cube in the sample data.System RequirementsThis tutorial was written using the following:Microsoft Windows 10 operating systemMicrosoft Excel 2016Pentaho Server 8.3 (Installation Instructions)XMLA Provider Driver for Windows provided by ARQuery (described in the next section)AcknowledgementsThe original spreadsheet used for this example was created by B. Madhusudan. The folks at ARQuery have a brief video tutorial on using the XMLA Provider as well.Instructions for downloading and installing the XMLA Provider are given on the next page.Pages: 1 2 3 4 5 6 Categories: Data Warehousing and Analytics, Microsoft Office Posted on January 10, 2020November 13, 2020 by holowczak Configuring Pentaho Data Integration to use the Oracle Wallet for Oracle Cloud IntroductionOracle Cloud databases use the Oracle wallet security. The Oracle Wallet contains cryptographic certificates and keys used to establish secure, encrypted communications between an Oracle client and the target Oracle database server. Pentaho Data Integration can be configured to use the Oracle Wallet to establish secure communications between PDI and an Oracle cloud server.This tutorial assumes you have the following components set up:An Oracle Cloud account with at least one Oracle database instance running. This tutorial (link here) provides the steps to set up an Oracle Cloud account and Autonomous Database. Connectivity between a client such as Oracle SQL Developer and the autonomous Database should be tested and confirmed.Pentaho Data Integration version 7.x or 8.x installed on Windows or MacOSX. Instructions for installing the latest version of PDI can be found here (Windows) and here (MacOSX). Pay particular attention to the version of Java development Kit used. As of January, 2019 Pentaho data Integration 8.3 running on Java Development Kit 1.8 are have been tested and found to work properly.This tutorial assumes PDI has been installed in C:\Pentaho\data-integration on Windows or /Users/ username /Pentaho/data-integration on MacOSX.In the next section, instructions for downloading and installing the Oracle JDBC Drivers are provided.Pages: 1 2 3 4 5 6 Categories: Data Warehousing and Analytics, Oracle Posted on December 31, 2019August 24, 2020 by holowczak Getting Started with Oracle Autonomous Database in the Cloud IntroductionThis tutorial covers how to set up a free Oracle Cloud account and create a free Autonomous Database instance.IMPORTANT: While the Oracle Cloud account is free and most of the basic services are free to use, a credit card (or similar payment method) is required to verify your identity. You will also need a cell phone capable of receiving text messages. If your intention is to only practice SQL statements and/or develop a web application using Oracle APEX, you can instead create a free Oracle APEX Workspace without using a credit card. A brief tutorial on working with Oracle APEX can be found here.Once the Autonomous Database is created, you can use several different tools to work with your data including Oracle SQL Developer, Oracle APEX and other tools. You may wish to download and install Oracle SQL Developer on your own computer (Windows or MacOSX).The first step will be to create a new Oracle Cloud account. These steps are outlined on the next page.Pages: 1 2 3 4 5 6 7 8 Categories: Cloud Computing, Oracle Posted on December 31, 2019August 13, 2020 by holowczak Getting Started with Oracle SQL Developer on MacOSX IntroductionThe purpose of this tutorial is to provide the steps necessary to install Oracle SQL Developer on the MacOSX operating system. Similar instructions for installing Oracle SQL Developer on Windows can be found at this link. This tutorial was created using MacOSX Mojave (version 10.14).Oracle SQL Developer is a powerful development tool with many features for interacting with Oracle databases. In addition to writing and submitting SQL statements using the SQL Worksheet, SQL Developer has powerful tools for managing databases, designing and reverse engineering databases using data models and running data mining jobs. The most recent version (Version 19.4 as of December 20, 2019) includes some basic Database Administration tools, support for REST queries and parsing JSON documents as well.Oracle SQL Developer requires an appropriate version of Java to be installed. This is perhaps the most confusing part of getting SQL Developer to work on MacOSX. Steps to install an appropriate Java Development Kit (JDK) are described on the next page.After installing JDK, the next section of this tutorial covers downloading and installing SQL Developer. If you already have SQL Developer downloaded and installed, you may wish to skip ahead to this page to learn how to create a new database connection.Towards the end of this tutorial, there are a few pages that cover how to write, run and save SQL statements, how to export data using SQL Developer and how to import data into Oracle using SQL Developer. You may also be interested in using SQL Developer to reverse engineer an Oracle schema. That tutorial is linked here. Pages: 1 2 3 4 5 6 Categories: Database, Oracle Posted on December 3, 2019June 5, 2020 by holowczak Getting Started with Oracle Application Express-APEX IntroductionOracle Application Express (APEX) is a web based application development environment created by Oracle. APEX leverages the Oracle relational database management system along with a middle tier application server to develop and deploy web based applications. In addition to these development tools, the APEX interface also provides a SQL editing tool and the ability to test SQL statements.Oracle offers free trial accounts to test the APEX development tools.This brief tutorial covers the following topics: Setting up a free Oracle APEX Workspace Uploading and running SQL scripts in Oracle APEX Running SQL queries in Oracle APEXEach of these is described in the following pages.Pages: 1 2 3 4 Categories: Database, Oracle Posted on November 26, 2019April 19, 2021 by holowczak Getting Started with NYC OpenData and the Socrata API IntroductionNYC OpenData provides a rich source of data sets reflecting many aspects of life in New York City. As of November, 2019, NYC OpenData has nearly 100 different data sets that are free to access, download and use. While all data sets can be explored and accessed using a web browser, it is often the case that for some projects, accessing and processing the data using software or a custom program is more efficient and can support the automation of such analysis.This tutorial uses one example NYC OpenData data set to demonstrate the Socrata Open Data Application Programming Interface (API). The Socrata Open Data API (SODA) can be used to identify and retrieve data from NYC OpenData as well as the public data sets from hundreds of other municipalities. Skills learned in this tutorial can be easily applied and extended to the thousands of other data sets available from around the country.There are four steps required to download NYC OpenData data using the Socrata API:Create a developer account Create an application and receive an App TokenLocate data of interest and obtain its API endpointUse the App Token and API endpoint to query and download dataEach of these steps will be covered in this tutorial. The Python programming language will be used to demonstrate the basic API features.Pages: 1 2 3 4 5 6 Categories: Data Warehousing and Analytics, Programming Posted on August 26, 2019April 26, 2021 by holowczak Database Management Systems Group Projects Semester Group ProjectsThe purpose of the semester-long group project is to give students an opportunity to put the theory of database modeling, implementation and application design into practice. The result will be a fully functioning database application.Students will be assigned to a group consisting of 5 total members. Groups will be responsible for:Develop a proposal for the project. First, groups should submit for approval their project ideas. This proposal should include: A separate cover page indicating the title of your project, the full names of the group members (with e-mail), the course number and course section. Include your assigned Group Number on the front page as well. A narrative description of the business used for the project or application being created. This should also include a description of the problem or opportunity being addressed. Identification of the information needs what information would help solve the problem or allow one to take advantage of the opportunity. Initial list of entities (tables) that have been identified. This should come naturally from the above discussions. Distribution of duties for the project. List the names of each group member and what their primary role will be (e.g., systems analyst, application developer, documentation writer).Informal discussions with the professor can help to refine the project and proposal.Groups should not continue working on the project unless it has been approved by the instructor. Groups may not choose a project that has already been completed for a different class or course. Systems Analysis The next step is for the group to take the requirements from the users and draw an Entity Relationship diagram using UML notation. The E-R Diagram should then be submitted to the professor for approval before proceeding. Students may use a specific E-R modeling tool such as MS Visio, LucidChart, or drawing tools available in MS Word or MS Powerpoint. Note: The Relationship View in MS Access is not an E-R modeling tool. Logical and Physical Modeling Given the E-R diagram and sets of attributes for each entity, the next step is to convert the E-R model into a relational model and go through the process of normalization. This step will require the group to list all of the functional dependencies. The normalized relations should be approved by the professor before proceeding. Database Implementation Groups should then implement the database tables from the normalized set of relations created in the previous step. For each normalized relation, write a SQL CREATE TABLE statement. Write separate ALTER TABLE statements to add PRIMARY KEY and FOREIGN KEY constraints to the tables. Data should be supplied for each table by writing SQL INSERT statements. The amount of data should be such that the need for a database is clear. In other words, provide enough examples to demonstrate why a database was required in the first place. Application Implementation The application (forms, reports, queries, menus or navigation form) can then be created on top of the tables. In general, a simple data entry form should be created for each table. However for the core business processes the group should create appropriate master/detail/lookup forms that guide the user through carrying out a business process. For example, if the business takes orders from customers, I am expecting a form with Orders and Order items with lookups for customer and products (or services). At least two reports that reflect the core of the business should also be created.For Queries, provide the associated SQL statements and a description of what the queries are used for.Create a Navigation Form that provides a starting place with access to all of the forms and reports in logical groupings. Write Up Final Report The final step is to prepare a formal report and brief presentation. This report should include:a) A separate cover page indicating the GROUP Number, the title of your project, the full names of the group members (with e-mail), the course number and course section.b) An introduction section similar to the proposal.c) Entity Relationship Model diagram.d) The collection of normalized relations and functional dependencies, and a brief discussion as to the normal form(s) achieved, the methods used to achieve these normal forms, and reasons why any de-normalization was done.e) The SQL DDL used to create the tables and add primary key and foreign key constraints. You may either add the Primary Key constraint to the CREATE TABLE statement or add it using a separate ALTER TABLE statement. Foreign Keys should be added with a separate ALTER TABLE statement. Your SQL code should be single spaced and use a Courier New (or similar fixed width) font.f) An example printout of each of the forms, reports and queries accompanied by a description of the function of each. Along with each form, include any VBA code that was written to embellish or customize the form.g) A picture of the Navigation form showing the organization of the different Forms and reports in the application. h) A narrative conclusion section that describes:* the software and services the group used to coordinate their activities * the group s experience with the project (which steps were the most difficult? Which were the easiest ? what did you learn that you did not imagine you would have? if you had to do it all over again, what would you do differently?)* if the proposed benefits can be realized by the new system* any final comments and conclusions Groups will periodically submit status updates and inter-group rankings of participation.Example ProjectClick here for a detailed walk-through of an example database project.Project Milestones (Updated 4/26/2021)Each group will submit an update and one or more Group Meeting Log Sheets on each of the following project milestones. February 17: Group Project Proposal submitted. File name: cis3400_proposal_groupXX.pdf March 1: Systems Analysis and E-R Model completed. File name: cis3400_model_groupXX.pdf March 22: Logical Modeling and Normalization. File name: cis3400_normalization_groupXX.pdf April 12: Physical Database implementation (using SQL CREATE TABLE and ALTER TABLE) completed (not the Application). File name: cis3400_sql_groupXX.pdf May 21: Final Project report due by 6:00PM via e-mail. File name: cis3400_report_groupXX.pdf. Be sure to put CIS 3400 in the Subject line and CC all group members.Extra Credit OpportunityIf your group has finished all other portions of the project and have available time and resources, you may elect to complete this extra credit task.Follow this tutorial to Migrate your MS Access database to Microsoft SQL Server. Your group will need to first set up a SQL Server database and install some additional Microsoft software (Windows only). All of the instructions for doing this are provided in the above tutorials.To document this work, include in the final Project Report a screen picture of the SQL Server Management Studio showing the migrated database tables and a screen picture of the MS Access database showing the links to the SQL Server database.Bad Project IdeasYou may not work on a project that has already been done. This includes projects that you find on the web and projects completed in prior semesters.You may not choose any of the following as project ideas: Restaurant (Customer/Menu/Order/Food) Hotel (Guest/Room/Reservation) Pizza Delivery (Customer/Order/Delivery) E-Commerce (Customer/Order/Product) File: projects.html Date: 9:55 AM 2/7/2021 All materials Copyright, 1997-2021 Richard Holowczak..... Donations WelcomeIf you have found anything educational or entertaining on holowczak.com please consider submitting a Donation using PayPal. Thanks!

TAGS:Tutorials com Holowczak 

<<< Thank you for your visit >>>

Hot Websites