This question found its way into my inbox yesterday:
I have a table with an object type column. I want to way to get the value of an attribute of that object type in my query. But Oracle keeps telling me "ORA-00904: invalid identifier". What am I doing wrong?Almost certainly what you are doing wrong is forgetting to use a table alias. Yeah, it's that simple.
For organizations that rely on the Cassandra NoSQL database but require more efficient search capabilities, Elassandra offers a compelling open-source solution. Elassandra combines the powers of Elasticsearch and Cassandra by utilizing Elasticsearch as a Cassandra secondary index. While companies may use both Elasticsearch and Cassandra on their own (and unite them by developing their own custom integration or synchronization code), Elassandra negates the challenges of implementing these measures and managing that software separately.
By closely integrating Elasticsearch with Cassandra, Elassandra provides search latencies that approach real-time responsiveness. Better yet, it achieves this while also delivering access to all the advantages of Elasticsearch’s established ecosystem of REST APIs, plugins, and other solutions. Through these tools — such as the powerful Kibana UI that allows users to search, analyze, and visualize data quickly and easily — database ops can be carried out with much more efficiency than is possible using Cassandra and Elasticsearch independently.
A recent Data Governance Survey conducted by Redgate of over 500 SQL Server professionals showed that 61% of respondents were using production data for non-production workflows; a process that is often seen as necessary for the reliability of development, testing, and other similar workflows. However, with data privacy legislation such as the GDPR, POPI, and HIPAA, we can often be prevented from utilizing this data unless we are able to anonymize/pseudonymize or mask it first.
When masking these sets of data, we are often required to mask a variety of data-types from names to credit card numbers and, unsurprisingly, email addresses. Ultimately, data copied into pre-production workflows should never compromise the security of data subjects stored in production; the most obvious blunder to avoid being never accidentally sending an email to all your contacts that exist in Test.
Although only available as a release candidate, MySQL 8 is already proving to be a huge leap forward in many regards. Error logging is no exception. The MySQL development team just announced that they have redesigned the error logging subsystem to use a new component architecture.
The redesign will allow the filtering of log events, as well as the routing of error log output to multiple destinations via the enabling of multiple sink components. This will make it possible to send error log events to third-party systems for additional formatting and analysis.
Some days ago, I blogged about a tutorial I wrote. The tutorial discusses how to combine serverless and Cloud Foundry for data retrieval and analytics. That scenario came up when I looked into regularly downloading GitHub traffic statistics for improved usage insights. What I needed was a mechanism to execute a small Python script on a daily or weekly basis. After looking into some possible solutions, IBM Cloud Functions was the clear winner. In this article, I am going to discuss how simple it is to implement some regular, automated activities, such as maintenance jobs for a cloud database.
Code Your ActionThe action is the part that is executed. IBM Cloud Functions supports several programming languages for coding an action. JavaScript, Swift, Python, and some others can be used, or even a Docker image provided. In my case, I implemented a Python action to fetch the GitHub account information and the list of repositories from Db2, then to retrieve the traffic data from GitHub and, last, to merge it in Db2. The code for that particular action can be found in this file on GitHub.
When we started using GraphQL in our Node.js project, we had a struggle with writing tests. We read numerous blogs, searching for the best way. Unfortunately, there didn't seem to be one, so we made one ourselves. Here, we'll share our way of testing GraphQL queries.
First, we will set up everything needed for running tests:
Hazelcast Jet supports writing into a number of third-party systems, including HDFS, Apache Kafka, and others. But what if you want to write into your own system that is not supported by Jet out-of-the-box? Starting with the version 0.6, Jet offers a new simple-to-use API for building custom Sinks and this tutorial will show you how to use it!
In this tutorial, we are going to build a JDBC sink writing Stock updates to a relational database, but you can apply the same principles for building an arbitrary sink.
Cucumber is a testing framework that supports behavior-driven development (BDD). It enables us to define the application requirements/behavior in simple English steps. It uses plain meaningful English text using simple grammar defined by a language called Gherkin. Gherkin is a domain-specific, business-readable language, using which we can describe the application’s behavior without defining the details of how behavior is implemented. Gherkin language serves two main purposes: documentation and automated tests.
Ruby is an open-source, dynamic programming language. It has a simple syntax and it’s also known as the layman’s programming language. Cucumber, the tool itself is written in Ruby language, and it can be used to test code written in any languages including Ruby. Tests written in Cucumber interacts directly with the development code, the tests are written in simple language that is easy to understand by all business stakeholders. The file where the entire application behavior is written in simple English-Gherkin mix is called as “Feature file”
MongoDB logical backup requires the use of the mongorestore tool to perform the restore backup. This article focuses on this tool and process.
Note: Percona develops a backup tool named Percona-Lab/mongodb-consistent-backup, which is a wrapper for mongodump, adding cluster-wide backup consistency. The backups created by mongodb_consistent_backup (in Dump/Mongodump mode) can be restored using the exact same steps as a regular mongodump backup — no special steps!
A lot has been documented about blockchain database merits. In this article, we will have a closer look at the blockchain as a database: pros, cons, and some specific details you can expect if you decide to use database blockchain.
Private BlockchainA private blockchain is comprised of an individual, practically closed network of nodes (blockchain participants). All nodes have a copy of the blockchain, which is a ledger that chronicles all the transactions that occurred within the network.
While using ODI to implement your ETLs, you may need to have loops. Let’s look at some examples where I will implement loops that will iterate n times (for loops) and loops that will iterate while they ensure the condition (while loops).
For LoopIn programming, we implement for loop as follows:
An agent is a Java process that's usually located on the server and listens to a port for incoming requests. It runs the requested scenario, reverse-engineers requested datastores, etc.
When a job submitted through ODI Studio GUI or through the startscen.sh agent gets scenario from the work repository and topology definitions from master repository, it combines and converts them into a runnable job, usually consisting of more than one code block. Then, it sends code blocks to destination environments, which may be DB servers, file servers, Hadoop name nodes, etc. Finally, the agent gets job statuses from these environments and writes into work repository tables for us to see from the Operator tab of ODI Studio.
The MDF files are the files that contain database information in SQL Server. They contain the startup information. The extension used is .mdf and it is the primary data file. The databases can contain one primary data file and several secondary data files or none.
In this article, we will show you how to work with errors related to the MDF file not opening.
Oracle Data Integrator is one of the most widely used ETL tools on the market, but from time to time, it may cause headaches when developers try to work together on the same project and try to modify shared objects. One of the problems is that objects that are forgotten get locked. Unless you have Supervisor grants, you will not be able to unlock the object. In this article, I will try to explain how to handle this problem without granting Supervisor to everyone.
Disclaimer: Use at your own risk. The method described in this post manipulates some records in Oracle Data Integrator's repository. Directly manipulating Oracle Data Integrator's repository tables may lead to unexpected behavior or crash of the software. The writer of the post can not be held liable because of the unexpected behaviors or crashes.
The Java Persistence Query Language (JPQL) is the most common way to query data from a database with JPA. But it supports only a small subset of the SQL standard and it also provides no support for database specific features.
So what should you do if you need to use a database-specific query feature or your DBA gives you a highly optimized query that cannot be transformed into JPQL? Just ignore it and do all the work in the Java code?
I use Extended Events almost exclusively for capturing query metrics. They are the most consistent and lowest-cost mechanism for getting the time and resources used by a query. They can be filtered, combined with other events... they're just marvelous... until you capture an execution plan.
Execution Plans in Extended EventsDon't get me wrong. Capturing execution plans with Extended Events is the way to go if you're attempting to automate the process of capturing plans on specific queries on an active system. It's Step 2 that bugs me. So, we capture the plan. Here's an example script. Captures all the various plans and the batch and puts 'em together using causality tracking:
Welcome to this week in Neo4j, where we round up what’s been happening in the world of graph databases in the last seven days. As my colleague Mark Needham is on his well-earned vacation, I’m filling in this week.
Next week we plan to do something different. Stay tuned!
At Strata last year, Kurt Brown from Netflix gaves a wonderful talk on how Netflix get's the most out of their data platform. The video is 37 minutes long and worth your time.
The list is specific to Netflix, but if you want you could make your own summary and apply it to your company. Here are the ones I want to call attention to the most.
In a previous post, I wrote about why you should (almost) always default to using bind variables. There are some exceptions, which I will cover in another follow-up post, but by default, bind variables are the right choice, both from a performance and from a security perspective.
In this article, I will show an example where regrettably, bind variables are not enough, and you can still run into significant performance issues in production. That’s when you create dynamic IN lists.
A common problem with dynamic SQL is parsing performance in production. What makes matters worse is that many developers do not have access to production environments, so they are unaware of the problem (even if there's nothing new about this topic). What exactly is the problem?
Execution Plan CachesMost database vendors these days ship with an execution plan cache (Oracle calls it cursor cache), where previously parsed SQL statements are stored and their execution plans are cached for reuse. This is the main reason why bind variables are so important (the other reason being SQL injection prevention). By using bind variables, we can make sure that the database will easily recognize an identical SQL statement from a previous execution and be able to re-execute the previously found execution plan.
In this post, we will cover how to start MongoDB database software in the three most used platforms: Windows, Linux, and MacOS.
If you have just started with NoSQL databases, you might wonder how to evaluate whether MongoDB is a good fit for your application.
When you launch SQL Server Management Studio (SSMS), you see the option to connect to an instance. If desired, you have the ability to browse instances running on your network. Just click on the drop-down and at the bottom, there is a Browse for more… option:
This allows you to browse for local or network instances. I have four instances running on my laptop. You can see them displayed in this list:
In this article, I will talk about temporal validity, which is a new feature that has come with Oracle 12c R1.
Temporal validity offers us an infrastructure with which we can render the data we have that is valid for a particular time range and query our table depending on the time. It’s a mechanism that allows us to examine the status of the data in the table or to use it in any time interval.
Apache® Cassandra™ is a popular open-source, distributed, key-value store columnar NoSQL database used by companies such as Netflix, eBay, and Expedia for key parts of their business. For Apache Cassandra users that need ad-hoc SQL query capabilities but are otherwise happy with their database choice, Apache® Ignite™ can enhance the performance of Apache Cassandra. For Apache Cassandra users or companies considering Cassandra that find it does not have the read speed or SQL capabilities they need in a distributed key-value store database, Apache Ignite can be a powerful alternative to Apache Cassandra.
Apache Cassandra Benefits and LimitationsThe features that make Apache Cassandra so appealing include:
Node.js and REST APIs go hand in hand. In fact, Ryan Dahl (the creator of Node.js) once described the focus of Node.js as "doing networking correctly." But where should you start when building a REST API with Node.js? What components should be used and how should things be organized? These are difficult questions to answer — especially when you're new to the Node.js ecosystem.
You could choose to use low-level packages and lots of custom code to build an API that's highly optimized for a specific workload. Or you could use an all-in-one framework like Sails.js, where many of the decisions have been made for you. There is no right or wrong answer: the best option will depend on the type of project you're working on and where you want to invest your time.
In this post, we’ll look at how to manage MongoDB bulk deletes and inserts with little impact on production traffic.
If you are like me, there is no end to the demands placed on you as a DBA. One of the biggest is when we want to load X% more data into the database, during peak traffic no less. I refer to this as MongoDB bulk deletes and inserts. As a DBA, my first reaction is, No, do this during off-peak hours. However, the business person in me says, What if this is due to clients loading a customer, product, or email list into the system for work during business hours? That puts it into another light, does it not?
In a recent webcast, we shared some tips and tricks on how you can operationalize MemSQL for configuring MemSQL Ops and MemSQL root user passwords; memory settings and health metrics; and how you can take backups and add nodes securely to your cluster.
Here are the topics we covered:
Content repositories/content stores are essential in the digital world. A content repository/content store is a data store of digital content with an associated set of data management features.
Typically, content repositories act as the storage engine for larger applications such as a content management system or a document management system.
Citus is an open-source extension to Postgres that transforms Postgres into a distributed database, scaling horizontally. The fact that Citus is built on top of Postgres is a huge benefit to our users: it means that when you choose Citus, you get all the great features that are available in Postgres. And Postgres itself is an awesome database. Awesome. As a team, we value the foundation we're built on and regularly aim to contribute back to it. We have a number of developers that have contributed to Postgres over the years from features like watch, event triggers, and the PostgreSQL extension framework.
Recently, a few more of our engineers expressed an interest in giving back to the PostgreSQL community. In fact, it's a common question: How can we better help the PostgreSQL project? And a common answer is reviewing patches. To help kick-start that process, we organized a session and carved out a few days just for patch review during the most recent commitfest.
In every organization, database plays an important role in holding the entire organization’s data across functions/departments. Traditional databases such as IBM, Microsoft, and Oracle control close to 80% of DBMS market with Oracle leading the pack. Oracle, MySQL, and SQL Server occupy the top three positions in the latest DB engine rankings. This explains the popularity of traditional databases.
In traditional databases, data inside the database is stored in tables and they have a logical connection among them. Hence, database solutions don’t have document design capabilities.
I have been working with SQL Server for 18 years and I was looking for a good way to develop my software, scripts, and stored procedures faster and more efficiently.
After testing several tools, I found this nice SQL Server add-on for SSMS.
SQL: It’s so last century, amirite?
Although it was created around ten years earlier, Structured Query Language (SQL) really began to catch hold in the commercial world during the mid-80s — and brought with it some great improvements over what had come before.
We can use SELECT...INTO in SQL Server to create a new table from a table source. SQL Server uses the attributes of the expressions in the SELECT list to define the structure of the new table.
Prior to SQL Server 2005, the use of SELECT...INTO in production code was a performance "code smell" because it acquired schema locks on system tables of the database, causing SQL Server to appear unresponsive while the query was being executed. This is because it is a DDL statement in an implicit transaction, which is inevitably long-running since the data is inserted within the same SQL Statement. However, this behavior was fixed in SQL Server 2005, when the locking model changed.
With the web server in place, it’s time to look into some database basics. As mentioned in the parent post, this series will use the Oracle Database driver/API for Node.js (node-oracledb) to interact with the database. In this post, you’ll create a module that’s responsible for starting up and shutting down a database connection pool. You’ll also add a function that simplifies executing simple statements by getting and releasing connections from the pool automatically.
Please note: This post is part of a series on creating a REST API with Node.js on Oracle Database. See that post for details on the project and links to other parts. Get the code here.
In this post, we're going to break down what constitutes a MongoDB replica set.
While replica sets are not a specific environment or solution, understanding what you can do with their sophisticated features helps fit a multitude of situations. With many classical databases, you need to use services, third-party software and scripts to facilitate many of the abilities of a replica set. We are going to explore how you might use each and end with a complicated diagram showing off these features.
In my first post, I gave a short summary of the meaning of . In this article, I’ll describe the most common architectures used to scale to big data sizes.
Database Hardware ArchitecturesOver the past 30 years there have been three primary architectures to deploy a database, including:
DZone Database Zone Fun With SQL: Functions in Postgres In our previous Fun with SQL post on the Citus Data blog, we covered w...
