Data, data en nog eens data

Author: tom

  • Oracle only listens to localhost

    —

    by

    tom
    in

    It happens to me quite often. I install Oracle and it only listens to the localhost. In a few steps, one can get it to listen to the IP address instead of the localhost. What is the problem if Oracle only listens to the localhost? This implies that the instance cannot be accessed from outside.…

  • Encryption in Oracle

    —

    by

    tom
    in

    Oracle has a nice facility to store data in an encrypted form. This allows to hide data content from users while still allowing them to see the columns. If one would like to use the encryption facilities, one must be given this grant: GRANT EXECUTE  ON dbms_crypto TO username; The code to store data in an encrypted…

  • Masking in Oracle

    —

    by

    tom
    in

    Oracle has recently introduced masking as one of the facilities. Masking allows data to be hidden from users / or only display part of the content. Other users can still the normal content. Let us take an example. A salary can shown as a random number to some users while others might still see the…

  • Variables in Oracle

    —

    by

    tom
    in

    The question is simple: we have an Oracle query and we want to use one search criterium as a parameter. Let us take a simple example. We want to use a parameter in the SQL statement: select * from doel where teller = parameter. So we can write the SQL only once and we can…

  • a view with security

    —

    by

    tom
    in

    One may implement security by creating a view. This view then only contains the columns that you would like to show. If one adds a filter, one may also limit the rows that are shown. The user that is allowed to read a view, only sees the columns and rows that are included in the…

  • drop Oracle table

    —

    by

    tom
    in

    Oracle knows the command “drop table”. One may include this in a script where a table is first deleted (using “drop table”) and subsequently rebuilt with additional SQL statement. But what happens if the table doesn’t exist? One may then end up with an error SQL Error: ORA-00942: “table or view does not exist”. How…

  • External tables in Oracle

    —

    by

    tom
    in

    External tables allow someone to store data in a file while these files are shown as tables within Oracle. So, we have a two sided object – on one hand it can be shown as a file that is human readable, on the other hand it can be seen as a table. Let us see…

  • Analysing JSON and database tables in Spark

    —

    by

    tom
    in

    In a previous note, I showed how CSV files can be analysed. One may use the same technique to analyse JSON files or tables in a database. First, analysing JSON files can be analysed with code that looks like: val jsonRDD = sc.wholeTextFiles(“/user/tom/baby_names.json”).map(x => x._2) val namesJson = sqlContext.read.json(jsonRDD) namesJson.registerTempTable(“names”) sqlContext.sql(“select * from names”).collect.foreach(println) Going…

  • Processing CSV files in Spark

    —

    by

    tom
    in

    It is possible to process CSV files in Spark as if these files are tables. That looks very promising: reading a file, translating the file into something that can be seen as a table and subsequently applying SQL to this. We need some additional jars however. First, we need to download 2 jars: http://mvnrepository.com/artifact/com.databricks/spark-csv_2.10 and…

  • Install Spark on windows

    —

    by

    tom
    in

    I found a beautiful YouTube movie that showed how Spark can be installed on windows. I found this on https://www.youtube.com/watch?v=WlE7RNdtfwE . The movie provided a clear guide how to this up. It provides a step by step approach. The first step is install JDK. I installed this from https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html. This allowed me install version 8,…